Search code examples
postgresqlprimary-keycreate-table

CREATE TABLE AS with PRIMARY KEY in one statement (PostgreSQL)

Is there a way to set the PRIMARY KEY in a single "CREATE TABLE AS" statement?

Example - I would like the following to be written in 1 statement rather than 2:

 CREATE TABLE "new_table_name" AS SELECT a.uniquekey, a.some_value + b.some_value FROM "table_a" AS a, "table_b" AS b WHERE a.uniquekey=b.uniquekey;
 ALTER TABLE "new_table_name" ADD PRIMARY KEY (uniquekey);

Is there a better way of doing this in general (assume there are more than 2 tables, e.g. 10)?

Solution

  • 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.