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