From: Michel SALAIS <msalais@msym.fr>
To: 'Nagaraj Raj' <nagaraj.sf@yahoo.com>
To: 'David Rowley' <dgrowleyml@gmail.com>
Cc: 'Justin Pryzby' <pryzby@telsasoft.com>
Cc: 'Pgsql-performance' <pgsql-performance@postgresql.org>
Cc: 'Michael Lewis' <mlewis@entrata.com>
Subject: RE: Partition with check constraint with "like"
Date: Sat, 22 May 2021 07:41:43 +0200
Message-ID: <006701d74ecd$2662f280$7328d780$@msym.fr> (raw)
In-Reply-To: <1954965228.496968.1621639708945@mail.yahoo.com>
References: <87840670.92362.1621557131080.ref@mail.yahoo.com>
<87840670.92362.1621557131080@mail.yahoo.com>
<CAApHDvoy=PR7SY=btN9Oiyv35RkD8ZbvuXaDaAfwreQqx7WW6w@mail.gmail.com>
<1347719485.144821.1621564574978@mail.yahoo.com>
<20210521023836.GH3676@telsasoft.com>
<81358482.168153.1621580571840@mail.yahoo.com>
<CAApHDvq1BkOqWjKPEtyrJ_Nj=3PuUAWw1S50U=apefu07_KuPg@mail.gmail.com>
<2126516115.347941.1621615110621@mail.yahoo.com>
<005201d74e84$4ee9be30$ecbd3a90$@msym.fr>
<2032281808.469685.1621637941028@mail.yahoo.com>
<1954965228.496968.1621639708945@mail.yahoo.com>
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.sf@yahoo.com>
Envoyé : samedi 22 mai 2021 01:28
À : 'David Rowley' <dgrowleyml@gmail.com>; Michel SALAIS <msalais@msym.fr>
Cc : 'Justin Pryzby' <pryzby@telsasoft.com>; 'Pgsql-performance' <pgsql-performance@postgresql.org>; Michael Lewis <mlewis@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.sf@yahoo.com <mailto:nagaraj.sf@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 <msalais@msym.fr <mailto:msalais@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.sf@yahoo.com <mailto:nagaraj.sf@yahoo.com> >
Envoyé : vendredi 21 mai 2021 18:39
À : David Rowley <dgrowleyml@gmail.com <mailto:dgrowleyml@gmail.com> >
Cc : Justin Pryzby <pryzby@telsasoft.com <mailto:pryzby@telsasoft.com> >; Pgsql-performance <pgsql-performance@postgresql.org <mailto:pgsql-performance@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 <dgrowleyml@gmail.com <mailto:dgrowleyml@gmail.com> > wrote:
On Fri, 21 May 2021 at 19:02, Nagaraj Raj <nagaraj.sf@yahoo.com <mailto:nagaraj.sf@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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: msalais@msym.fr, nagaraj.sf@yahoo.com, dgrowleyml@gmail.com, pryzby@telsasoft.com, mlewis@entrata.com
Subject: RE: Partition with check constraint with "like"
In-Reply-To: <006701d74ecd$2662f280$7328d780$@msym.fr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox