Обсуждение: Creating a partition table
Hi all, I have a PostgreSQL server version 11.3 that my devs are asking for some partitioned tables. I can’t seem to get the syntaxcorrect though. My SQL code is: CREATE TABLE schema.temp_table AS (SELECT * FROM schema.original_table) PARTITION BY LIST(attribute_name); As what I want to do is create a copy of the current table, create the partitions on the new temp table, and when everythingworks, drop the original table and do an alter table rename on the new one. The error is: ERROR: syntax error at or near “PARTITION” Line 3: PARTITION BY LIST(attribute_name); I’ve tried using the word RANGE as opposed to LIST, but the error remains. Does PostgreSQL not support a construct like thisone or am I doing something wrong? — Jay Sent from my iPad
On Fri, 2020-10-30 at 12:52 -0400, John Scalia wrote: > I have a PostgreSQL server version 11.3 that my devs are asking for some partitioned tables. I can’t seem to get the syntaxcorrect though. My SQL code is: > > CREATE TABLE schema.temp_table > AS (SELECT * FROM schema.original_table) > PARTITION BY LIST(attribute_name); > > As what I want to do is create a copy of the current table, create the partitions on the new temp table, and when everythingworks, drop the original table and do an alter table rename on the new > one. > > The error is: > ERROR: syntax error at or near “PARTITION” > Line 3: PARTITION BY LIST(attribute_name); > > I’ve tried using the word RANGE as opposed to LIST, but the error remains. Does PostgreSQL not support a construct likethis one or am I doing something wrong? You cannot do that in a single step. For one, you didn't create partitions. Do it like this: CREATE TABLE temp_table (LIKE original_table INCLUDING ALL) PARTITION BY LIST(attribute_name); CREATE TABLE temp_table_1 PARTITION OF temp_table FOR VALUES IN (1); CREATE TABLE temp_table_2_3 PARTITION OF temp_table FOR VALUES IN (2, 3); INSERT INTO temp_table SELECT * FROM original_table; Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
John Scalia <jayknowsunix@gmail.com> writes:
> I have a PostgreSQL server version 11.3 that my devs are asking for some partitioned tables. I can’t seem to get the
syntaxcorrect though. My SQL code is:
> CREATE TABLE schema.temp_table
> AS (SELECT * FROM schema.original_table)
> PARTITION BY LIST(attribute_name);
You can't create and populate a partitioned table in one command; you have
to create some partitions before you can load data into it.
I believe you could use CREATE TABLE temp_table (LIKE original_table)
PARTITION BY LIST(attribute_name) to avoid manually repeating the
column list. Check the docs to see which other LIKE options you want
to use.
regards, tom lane
Well, originally my code had a WITH NO DATA before the PARTITION BY clause, but that was error in as well, but thinkingthat I would just get structure of the table that way, the error didn’t make any sense. I’ll try Laurenz’s code example and see what happens. Sent from my iPad > On Oct 30, 2020, at 1:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > John Scalia <jayknowsunix@gmail.com> writes: >> I have a PostgreSQL server version 11.3 that my devs are asking for some partitioned tables. I can’t seem to get the syntaxcorrect though. My SQL code is: > >> CREATE TABLE schema.temp_table >> AS (SELECT * FROM schema.original_table) >> PARTITION BY LIST(attribute_name); > > You can't create and populate a partitioned table in one command; you have > to create some partitions before you can load data into it. > > I believe you could use CREATE TABLE temp_table (LIKE original_table) > PARTITION BY LIST(attribute_name) to avoid manually repeating the > column list. Check the docs to see which other LIKE options you want > to use. > > regards, tom lane
Ok I tried what Laurent had suggested and now the error is: Insufficient columns in PRIMARY KEY constraint definition. Now, the attribute the devs want is not a key value in the original table, but if it were that wouldn’t make a great dealof sense for a partition. So, now why did it gripe? Sent from my iPad > On Oct 30, 2020, at 1:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > John Scalia <jayknowsunix@gmail.com> writes: >> I have a PostgreSQL server version 11.3 that my devs are asking for some partitioned tables. I can’t seem to get the syntaxcorrect though. My SQL code is: > >> CREATE TABLE schema.temp_table >> AS (SELECT * FROM schema.original_table) >> PARTITION BY LIST(attribute_name); > > You can't create and populate a partitioned table in one command; you have > to create some partitions before you can load data into it. > > I believe you could use CREATE TABLE temp_table (LIKE original_table) > PARTITION BY LIST(attribute_name) to avoid manually repeating the > column list. Check the docs to see which other LIKE options you want > to use. > > regards, tom lane
John Scalia <jayknowsunix@gmail.com> writes:
> Ok I tried what Laurent had suggested and now the error is:
> Insufficient columns in PRIMARY KEY constraint definition.
> Now, the attribute the devs want is not a key value in the original table, but if it were that wouldn’t make a great
dealof sense for a partition. So, now why did it gripe?
FWIW, we recently rephrased that message as "unique constraint on
partitioned table must include all partitioning columns", because
people were finding it confusing.
The reason for the restriction is that a partitioned index is really
just a collection of separate indexes on the individual leaf partitions
(just as the partitioned table is a collection of plain tables).
So it can't enforce uniqueness across partitions unless such uniqueness
is guaranteed by the partitioning scheme.
Yeah, this is an implementation restriction, but I don't foresee it
ever going away. If the indexes weren't independent then you'd lose
the scalability benefits that partitioning is meant to provide.
regards, tom lane