RE: Partition with check constraint with "like"

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(dot)sf(at)yahoo(dot)com>
Envoyé : samedi 22 mai 2021 01:28
À : 'David Rowley' <dgrowleyml(at)gmail(dot)com>; Michel SALAIS <msalais(at)msym(dot)fr>
Cc : 'Justin Pryzby' <pryzby(at)telsasoft(dot)com>; 'Pgsql-performance' <pgsql-performance(at)postgresql(dot)org>; Michael Lewis <mlewis(at)entrata(dot)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(dot)sf(at)yahoo(dot)com <mailto:nagaraj(dot)sf(at)yahoo(dot)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 <msalais(at)msym(dot)fr <mailto:msalais(at)msym(dot)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(dot)sf(at)yahoo(dot)com <mailto:nagaraj(dot)sf(at)yahoo(dot)com> >
Envoyé : vendredi 21 mai 2021 18:39
À : David Rowley <dgrowleyml(at)gmail(dot)com <mailto:dgrowleyml(at)gmail(dot)com> >
Cc : Justin Pryzby <pryzby(at)telsasoft(dot)com <mailto:pryzby(at)telsasoft(dot)com> >; Pgsql-performance <pgsql-performance(at)postgresql(dot)org <mailto:pgsql-performance(at)postgresql(dot)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 <dgrowleyml(at)gmail(dot)com <mailto:dgrowleyml(at)gmail(dot)com> > wrote:

On Fri, 21 May 2021 at 19:02, Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com <mailto:nagaraj(dot)sf(at)yahoo(dot)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