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
