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

postgres create table foreign key
postgresql create table if not exists
postgres add primary key if not exists
postgres primary key
postgres create table like
postgres composite primary key
postgres composite foreign key
postgres create table with index

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

Documentation: 9.0: CREATE TABLE, But the CREATE TABLE command can add defaults and constraints to the table The primary key constraint specifies that a column or columns of a table can� Only one primary key can be specified for a table, whether as a column constraint or a table constraint. The primary key constraint should name a set of columns that is different from the set of columns named by any unique constraint defined for the same table.

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

PostgreSQL CREATE TABLE, In this syntax: First, specify the name of the table after the CREATE TABLE keywords. Second, creating a table that already exists will result in a error. Third, specify a comma-separated list of table columns. Finally, specify the table constraints including primary key, foreign key, and check constraints. The PostgreSQL CREATE TABLE statement is used to create a new table in any of the given database. Syntax. Basic syntax of CREATE TABLE statement is as follows − CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype, .. columnN datatype, PRIMARY KEY( one or more columns ) );

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

PostgreSQL Primary Key, The following statement creates a purchase order (PO) header table with the name po_headers . CREATE TABLE po_headers ( po_no INTEGER PRIMARY KEY,� 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);

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)

PostgreSQL: CREATE TABLE Statement, The PostgreSQL CREATE TABLE statement allows you to create and define a table. a table called order_details which has 5 columns and one primary key:. See the PostgreSQL CREATE TABLE statement for more detailed CREATE TABLE syntax as this is an over-simplification to demonstrate how to create a Primary Key. constraint_name The name of 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

done...

PostgreSQL PRIMARY KEY constraint, The PostgreSQL PRIMARY KEY is a column in a table which must contain a CREATE TABLE orders( ord_no integer NOT NULL UNIQUE,� A PRIMARY KEY can be represented for one column or combination of columns. No NULL value can not be accepted in PRIMARY KEY. PostgreSQL PRIMARY KEY example. SQL. CREATE TABLE orders( ord_no integer NOT NULL UNIQUE, ord_date date, item_name character(35), item_grade character(1), ord_qty numeric, ord_amount numeric ); The table structure

PostgreSQL - CREATE Table, The syntax will become clear with an example given below. Examples. The following is an example, which creates a COMPANY table with ID as primary key and� One primary key can be specified for a table, whether as a column constraint or a table constraint. The primary key constraint should name a set of columns that is different from other sets of columns named by any unique constraint defined for the same table.

PRIMARY KEY, When you specify a primary key, PSQL creates a unique index with the specified a DROP clause in an ALTER TABLE statement and add the new primary key. There are so many ways of creating table. User can create table using primary key or unique key also. CREATE TABLE statement is used to create the table in oracle. sql create table without primary key : User can create table without using primary key and with using primary key. The simple CREATE TABLE statement is used to create a table.

Postgres Serial Primary Key, It's most often used in the primary key column of a table. You can use the following syntax to create a table with a column that contains a� Introduction to PostgreSQL Foreign Key Constraint. A foreign key is a column or a group of columns in a table that reference the primary key of another table. The table that contains the foreign key is called the referencing table or child table. And the table referenced by the foreign key is called the referenced table or parent table.

Comments
  • Of the two options, creating the primary key (and its associated index) after data is loaded will probably be faster.
  • Downvoted since this is mysql syntax, not postgres (per the question). And not possible in postgres.
  • 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...
  • Upper vote, even if it was not the question it was useful for me ;)
  • That's invalid for Postgres
  • Looks like Oracle syntax