Re: Partition with check constraint with "like"

On Fri, 21 May 2021 at 12:32, Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com> wrote:
> I am trying to create partitions on the table based on first letter of the column record value using inherit relation & check constraint.

You'll get much better performance out of native partitioning than you
will with the old inheritance method of doing it.

> EXECUTE 'CREATE TABLE partition_tab.' || c_table || '(check ( name like '''|| chk_cond||''')) INHERITS (' ||TG_TABLE_NAME|| ');';

This is a bad idea. There's a lock upgrade hazard here that could end
up causing deadlocks on INSERT. You should just create all the tables
you need beforehand.

I'd recommend you do this using RANGE partitioning. For example:

create table mytable (a text not null) partition by range (a);
create table mytable_a partition of mytable for values from ('a') to
('b'); -- note the upper bound of the range is non-inclusive.
create table mytable_b partition of mytable for values from ('b') to ('c');
insert into mytable values('alpha'),('bravo');

explain select * from mytable where a = 'alpha';
QUERY PLAN
-------------------------------------------------------------------
Seq Scan on mytable_a mytable (cost=0.00..27.00 rows=7 width=32)
Filter: (a = 'alpha'::text)
(2 rows)

The mytable_b is not scanned.

David