From: Tom Lane <tgl@sss.pgh.pa.us>
To: John Scalia <jayknowsunix@gmail.com>
cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: Creating a partition table
In-reply-to: <BFBB87D2-4AFB-4B2A-A140-E19860849936@gmail.com>
References: <BFBB87D2-4AFB-4B2A-A140-E19860849936@gmail.com>
Comments: In-reply-to John Scalia <jayknowsunix@gmail.com>
	message dated "Fri, 30 Oct 2020 12:52:49 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <98824.1604077633.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 30 Oct 2020 13:07:13 -0400
Message-ID: <98825.1604077633@sss.pgh.pa.us>
Precedence: bulk

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=E2=80=99t 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