CREATE TABLE AS with PRIMARY KEY in one statement (PostgreSQL)
If you want to create a new table with the same table structure of another table, you can do this in one statement (both creating a new table and setting the primary key) like this:
CREATE TABLE mytable_clone (
LIKE mytable
INCLUDING defaults
INCLUDING constraints
INCLUDING indexes
);
No, there is no shorter way to create the table and the primary key.
See the command below, it will create a new table with all the constraints and with no data. Worked in postgres 9.5
CREATE TABLE IF NOT EXISTS <ClonedTableName>(like <OriginalTableName> including all)
According to the manual: create table and create table as you can either:
- create table with primary key first, and use select into later
- create table as first, and use add primary key later
But not both create table as with primary key - what you wanted.