CREATE TABLE AS SELECT in PostgreSQL

Question

CREATE TABLE AS SELECT statement in PostgreSQL is not creating the new table with the columns with their NOT NULL constraint of the original table.

My table tab1 is having col1 and col2 with not null constraints.

create table tab1 (col1 varchar(23) not null, col2 int not null)

and now i am creating a new table tab2 with definition and data of the table tab2 as below:

create table tab2 as select col1,col2 from tab1

But col1 and col2 of tab2 is not having NOT NULL constraint. How to get the exact NOT NULL constraint status in the new table columns as of the original table?

If there is any way to achieve this using CREATE TABLE AS SELECT statement that is greatly appreciated. CREATE TABLE AS SELECT statement in Oracle,DB2 and SELECT .. INTO in SQLServer copy the NOT NULL CONSTRAINT along with the data.

possible duplicate of creating table as select is droping the not null constraints in postgresql — user330315
– user330315, Commented Oct 12, 2011 at 11:44
The same thing has been asked 1 hour ago. Btw: In Oracle a 'CREATE TABLES AS SELECT' does not copy constraints either. — user330315
– user330315, Commented Oct 12, 2011 at 11:45
@a_horse_with_no_name I just tested in Oracle where it is perfectly copying the NOT NULL constraint to the new table's columns as of the parent table. From the similar thread, i can use INCLUDE cluase to copy the contraints but how can i use this if i hvae to create a new table from a query which as UNION ALL etc. like below: CREATE TABLE tab3 AS SELECT * FROM TAB1 UNION ALL SELECT * FROM TAB2 This is the actual requirement for me. — vchitta
– vchitta, Commented Oct 13, 2011 at 5:34
@vchitta, I don't understand what sort of spec you want here. Suppose TAB1's first column has a NOT NULL constraint but TAB2 does not, even has NULL values in it?! Can you get what you want with a combination of CREATE TABLE ... (LIKE ...) and INSERT? — Andrew Lazarus
– Andrew Lazarus, Commented Oct 20, 2011 at 4:58

Andrew Lazarus · Accepted Answer · 2011-10-20 04:55:08Z

8

In PostgreSQL the functionality you are looking for is in

CREATE TABLE tablename 
(LIKE parent_table INCLUDING CONSTRAINTS) /* other options too */

CREATE TABLE AS, as far as I know, can be used with a totally arbitrary SELECTand PG does not check if this involves only one table, much less try to deduce constraints.

answered Oct 20, 2011 at 4:55

Andrew Lazarus

19.5k3 gold badges39 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

CREATE TABLE AS SELECT in PostgreSQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related