sorry, forgot to attach the test cases.Postgres 13 | db<>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> 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>
wrote:
#yiv1392522220 -- filtered {}#yiv1392522220 filtered {}#yiv1392522220 filtered
{}#yiv1392522220 filtered {}#yiv1392522220 filtered {}#yiv1392522220
p.yiv1392522220MsoNormal, #yiv1392522220 li.yiv1392522220MsoNormal,
#yiv1392522220 div.yiv1392522220MsoNormal
{margin:0cm;font-size:11.0pt;font-family:sans-serif;}#yiv1392522220 a:link,
#yiv1392522220 span.yiv1392522220MsoHyperlink
{color:blue;text-decoration:underline;}#yiv1392522220
span.yiv1392522220EmailStyle20
{font-family:sans-serif;color:windowtext;}#yiv1392522220
.yiv1392522220MsoChpDefault {font-size:10.0pt;}#yiv1392522220 filtered
{}#yiv1392522220 div.yiv1392522220WordSection1 {}#yiv1392522220
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>
Envoyé : vendredi 21 mai 2021 18:39
À : David Rowley <dgrowle...@gmail.com>
Cc : Justin Pryzby <pry...@telsasoft.com>; Pgsql-performance
<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>
wrote:
On Fri, 21 May 2021 at 19:02, Nagaraj Raj <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
