CODEWITHSUNDEEP
sql
amar
amar

Reputation: 31

creating table as select is dropping the not null constraints in postgresql

in postgres sql creating the table as select dropped the not null constraints on the table.

for example :

create table A (char a not null);
create table B as select * from a;
select * from B;-- no constraint is copied from A table

please let me know how to copy table data as well as constraints in postgres.

Upvotes: 3

Views: 1982

Answers (2)

Zohaib
Zohaib

Reputation: 7116

very detailed and nicely explained tutorial for create table command in PostgreSQL 9.1

http://www.postgresql.org/docs/current/static/sql-createtable.html

Not null constraints are always copied (if creating table by giving reference of parent table in create table command) and even with including constraints, only check constraint will be copied.

Upvotes: 0

user330315
user330315

Reputation:

There is no single-command solution to this.

To create a table based on an existing one, including all constraints, use:

create table B ( like a including constraints);

Once you have done that, you can copy the data from the old one to the new one:

insert into b
select * from a;

If you do this in a single transaction, it looks like an atomic operation to all other sessions connected to the database.

Upvotes: 9

Related Questions