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

postgresql primary-key create-table
TimY picture TimY · Jun 24, 2012 · Viewed 36.2k times · Source

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)?

Answer

peenut picture peenut · Jun 24, 2012

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.

Related questions

How to reset postgres' primary key sequence when it falls out of sync?

I ran into the problem that my primary key sequence is not in sync with my table rows. That is, when I insert a new row I get a duplicate key error because the sequence implied in the serial datatype …

Read More
How to add an auto-incrementing primary key to an existing table, in PostgreSQL?

I have a table with existing data. Is there a way to add a primary key without deleting and re-creating the table?

Read More
Reset PostgreSQL primary key to 1

Is there a way to reset the primary key of a PostgreSQL table to start at 1 again on a populated table? Right now it's generating numbers from 1000000 and up. I want it all to reset and start to 1, keeping all …

Read More