pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedCreating a partition table
6+ messages / 3 participants
[nested] [flat]
* Creating a partition table
@ 2020-10-30 16:52 John Scalia <jayknowsunix@gmail.com>
2020-10-30 17:01 ` Re: Creating a partition table Laurenz Albe <laurenz.albe@cybertec.at>
2020-10-30 17:07 ` Re: Creating a partition table Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 6+ messages in thread
From: John Scalia @ 2020-10-30 16:52 UTC (permalink / raw)
To: pgsql-admin
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 syntax correct 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 everything works, 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 this one or am I doing something wrong?
—
Jay
Sent from my iPad
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Creating a partition table
2020-10-30 16:52 Creating a partition table John Scalia <jayknowsunix@gmail.com>
@ 2020-10-30 17:01 ` Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 0 replies; 6+ messages in thread
From: Laurenz Albe @ 2020-10-30 17:01 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; pgsql-admin
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 syntax correct 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 everything works, 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 this 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Creating a partition table
2020-10-30 16:52 Creating a partition table John Scalia <jayknowsunix@gmail.com>
@ 2020-10-30 17:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-10-30 17:48 ` Re: Creating a partition table John Scalia <jayknowsunix@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2020-10-30 17:07 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: pgsql-admin
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 syntax correct 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Creating a partition table
2020-10-30 16:52 Creating a partition table John Scalia <jayknowsunix@gmail.com>
2020-10-30 17:07 ` Re: Creating a partition table Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-10-30 17:48 ` John Scalia <jayknowsunix@gmail.com>
2020-10-30 18:09 ` Re: Creating a partition table Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: John Scalia @ 2020-10-30 17:48 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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 deal of 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 syntax correct 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Creating a partition table
2020-10-30 16:52 Creating a partition table John Scalia <jayknowsunix@gmail.com>
2020-10-30 17:07 ` Re: Creating a partition table Tom Lane <tgl@sss.pgh.pa.us>
2020-10-30 17:48 ` Re: Creating a partition table John Scalia <jayknowsunix@gmail.com>
@ 2020-10-30 18:09 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2020-10-30 18:09 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: pgsql-admin
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 deal of 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Creating a partition table
@ 2020-10-30 17:40 John Scalia <jayknowsunix@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: John Scalia @ 2020-10-30 17:40 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Well, originally my code had a WITH NO DATA before the PARTITION BY clause, but that was error in as well, but thinking that 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 syntax correct 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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2020-10-30 18:09 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-10-30 16:52 Creating a partition table John Scalia <jayknowsunix@gmail.com>
2020-10-30 17:01 ` Laurenz Albe <laurenz.albe@cybertec.at>
2020-10-30 17:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-10-30 17:48 ` John Scalia <jayknowsunix@gmail.com>
2020-10-30 18:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-10-30 17:40 Re: Creating a partition table John Scalia <jayknowsunix@gmail.com>
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