70

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

TimY
  • 5,256
  • 5
  • 44
  • 57

6 Answers6

75

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.

peenut
  • 3,366
  • 23
  • 24
  • 20
    Of the two options, creating the primary key (and its associated index) *after* data is loaded will probably be faster. – kgrittn Jun 24 '12 at 16:41
25

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
);
Andrii Abramov
  • 10,019
  • 9
  • 74
  • 96
francs
  • 8,511
  • 7
  • 39
  • 43
1

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)
user1188867
  • 3,726
  • 5
  • 43
  • 69
0

No, there is no shorter way to create the table and the primary key.

0

well in mysql ,both is possible in one command

the command is 

create table new_tbl (PRIMARY KEY(`id`)) as select * from old_tbl;

where id is column with primary key of old_tbl

done...

kaushikC
  • 77
  • 3
  • 4
    Downvoted since this is mysql syntax, not postgres (per the question). And not possible in postgres. – erikreed Sep 21 '17 at 17:24
  • 6
    well I have exact same problem in mysql at same time of question posted and I myself solved it with this command ,so i think this may be usefull to my friend here.(beside i am not allowed to comment yet,and thought that SQL syntax will be same for Postgre SQL,so i posted this as answer) anyway thanks for exapnding my knowledge about postgre sql... – kaushikC Sep 22 '17 at 02:11
  • 4
    Upper vote, even if it was not the question it was useful for me ;) – karen Nov 22 '17 at 09:30
-6

You may do this way

CREATE TABLE IOT (EMPID,ID,Name, CONSTRAINT PK PRIMARY KEY( ID,EMPID)) 
 ORGANIZATION INDEX NOLOGGING COMPRESS 1 PARALLEL 4
AS SELECT 1 as empid,2 id,'XYZ' Name FROM dual;
user5198207
  • 1