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 nul开发者_StackOverflow中文版l);
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.

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.

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.