Re: Partition with check constraint with "like"

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Fri, May 21, 2021 at 02:36:14AM +0000, Nagaraj Raj wrote:
>  Thank you. This is a great help. 
> But "a" have some records with alpha and numeric. 

So then you should make one or more partitions FROM ('1')TO('9').

> example :
> insert into mytable values('alpha'),('bravo');
> insert into mytable values('1lpha'),('2ravo');
> 
> 
>     On Thursday, May 20, 2021, 06:23:14 PM PDT, David Rowley <dgrowleyml@xxxxxxxxx> wrote:  
>  
>  On Fri, 21 May 2021 at 12:32, Nagaraj Raj <nagaraj.sf@xxxxxxxxx> 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.





[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux