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.