Hi,
Then we must know what is your collation… What is the collation of your database? select datname, pg_catalog.pg_encoding_to_char(encoding) "encoding", datcollate, datctype from pg_database; It is also possible to define an explicit collation for the column. You can have it when you describe the table… But I think like others have already said that this is perhaps not the right choice. Michel SALAIS De : Nagaraj Raj <nagaraj...@yahoo.com> Envoyé : samedi 22 mai 2021 01:28 À : 'David Rowley' <dgrowle...@gmail.com>; Michel SALAIS <msal...@msym.fr> Cc : 'Justin Pryzby' <pry...@telsasoft.com>; 'Pgsql-performance' <pgsql-performa...@postgresql.org>; Michael Lewis <mle...@entrata.com> Objet : Re: Partition with check constraint with "like" sorry, forgot to attach the test cases. Postgres 13 | db <https://dbfiddle.uk/?rdbms=postgres_13&fiddle=602350db327ee6215837bbf48f0763f8> <>fiddle Postgres 13 | db<>fiddle Free online SQL environment for experimenting and sharing. On Friday, May 21, 2021, 03:59:18 PM PDT, Nagaraj Raj <nagaraj...@yahoo.com <mailto:nagaraj...@yahoo.com> > wrote: Hi, This is also not working, create table mytable_z partition of mytable for values from ('Z') to ('[') partition by range(id); ERROR: empty range bound specified for partition "mytable_z" DETAIL: Specified lower bound ('Z') is greater than or equal to upper bound ('['). SQL state: 42P17 DB running on version PostgreSQL 11.6, compiled by Visual C++ build 1800, 64-bit On Friday, May 21, 2021, 02:00:38 PM PDT, Michel SALAIS <msal...@msym.fr <mailto:msal...@msym.fr> > wrote: Hi I don’t discuss here the choice itself but this is not correct: create table mytable_z of mytable for values from ('Z') to ('Z['); It should be create table mytable_z of mytable for values from ('Z') to ('[') Michel SALAIS De : Nagaraj Raj <nagaraj...@yahoo.com <mailto:nagaraj...@yahoo.com> > Envoyé : vendredi 21 mai 2021 18:39 À : David Rowley <dgrowle...@gmail.com <mailto:dgrowle...@gmail.com> > Cc : Justin Pryzby <pry...@telsasoft.com <mailto:pry...@telsasoft.com> >; Pgsql-performance <pgsql-performa...@postgresql.org <mailto:pgsql-performa...@postgresql.org> > Objet : Re: Partition with check constraint with "like" Hi David, Hi, I am trying to create partitions on the table which have around 2BIL records and users will always look for the "name", its not possible to create a partition with a list, so we are trying to create a partition-based first letter of the name column. name column has a combination of alpha numeric values. > postgres=# select chr(ascii('z')+1) ; > chr > ----- > { > (1 row) I tried as below, I'm able to create a partition table for 'Z', but it's not identifying partition table. postgres=# select chr(ascii('Z')+1) ; chr ----- [ (1 row) create table mytable_z of mytable for values from ('Z') to ('Z['); CREATE TABLE insert into mytable values(4,'ZAR83NB'); ERROR: no partition of relation "mytable" found for row DETAIL: Partition key of the failing row contains (name) = (ZAR83NB). SQL state: 23514 On Friday, May 21, 2021, 01:24:13 AM PDT, David Rowley <dgrowle...@gmail.com <mailto:dgrowle...@gmail.com> > wrote: On Fri, 21 May 2021 at 19:02, Nagaraj Raj <nagaraj...@yahoo.com <mailto:nagaraj...@yahoo.com> > wrote: > then what would be the range of Z > FROM (Z) to (?) ; postgres=# select chr(ascii('z')+1) ; chr ----- { (1 row) > same way for 9 postgres=# select chr(ascii('9')+1) ; chr ----- : (1 row) https://en.wikipedia.org/wiki/ASCII You can also use MINVALUE and MAXVALUE to mean unbounded at either end of the range. But is there a particular need that you want to partition this way? It seems like it might be a bit painful to maintain, especially if you're not limiting yourself to ASCII or ANSI characters. You might want to consider HASH partitioning if you're just looking for a way to keep your tables and indexes to a more manageable size. You've not really mentioned your use case here, so it's hard to give any advice. There are more details about partitioning in https://www.postgresql.org/docs/current/ddl-partitioning.html David
