On Mon, Apr 19, 2010 at 08:32, Nikhil Sontakke <nikhil.sonta...@enterprisedb.com> wrote: > Hi, > > I saw this behavior with PG head: > > postgres=# create table x(x int default 8 not null); > CREATE TABLE > postgres=# create table x1 as select * from x; > SELECT 0 > postgres=# \d x > Table "public.x" > Column | Type | Modifiers > --------+---------+-------------------- > x | integer | not null default 8 > > postgres=# \d x1 > Table "public.x1" > Column | Type | Modifiers > --------+---------+----------- > x | integer | > > Note that column x for table x1 did not get the not null modifier. It > also did not get the default values. > > Was wondering what are the standards for CTAS. Oracle seems to honor > the NOT NULL modifier. This might be a bug if we do not honor > modifiers in CTAS.
Given that CREATE TABLE AS creates a table based on the result of a query, it seems pretty logical that constraints wouldn't be copied over - they're part of the table, they're not visible in a query result. The documentation pretty clearly says you should use CREATE TABLE LIKE if you want to copy the constraints over, if you look at the CREATE TABLE manpage (not on the CREATE TABLE AS though - perhaps a note should be added there?) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
