Thread: CTAS not honoring NOT NULL, DEFAULT modifiers
Guess no-one got to read this email. I sent it to pgsql-patches without realizing that it is a dead-list. Shouldn't we atleast bounce emails back to senders if they send an email to pgsql-patches? Regards, NIkhils ---------- Forwarded message ---------- From: Nikhil Sontakke <nikhil.sontakke@enterprisedb.com> Date: Fri, Apr 2, 2010 at 6:07 PM Subject: CTAS not honoring NOT NULL, DEFAULT modifiers To: pgsql-patches@postgresql.org 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. Regards, Nikhils -- http://www.enterprisedb.com -- http://www.enterprisedb.com
On Mon, Apr 19, 2010 at 08:32, Nikhil Sontakke <nikhil.sontakke@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 HaganderMe: http://www.hagander.net/Work: http://www.redpill-linpro.com/
>> 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. > Yeah agreed, it is just a SELECT query afterall. > 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?) > I think the semantics should be pretty ok as is. But I saw another DB honoring the NOT NULL modifiers and hence the wonder if there is something about this in the standards. Regards, Nikhils -- http://www.enterprisedb.com
Nikhil Sontakke <nikhil.sontakke@enterprisedb.com> writes:
> I think the semantics should be pretty ok as is. But I saw another DB
> honoring the NOT NULL modifiers and hence the wonder if there is
> something about this in the standards.
Actually, SQL:2008 does say that if an output column of the SELECT is
known not nullable, then the created table should have the NOT NULL
property for that column. We don't implement anything about "known not
nullable", though, so I'd view this as a small part of an unimplemented
SQL feature. The usefulness seems rather debatable anyway.
regards, tom lane
Tom Lane wrote: > Nikhil Sontakke <nikhil.sontakke@enterprisedb.com> writes: > > I think the semantics should be pretty ok as is. But I saw another DB > > honoring the NOT NULL modifiers and hence the wonder if there is > > something about this in the standards. > > Actually, SQL:2008 does say that if an output column of the SELECT is > known not nullable, then the created table should have the NOT NULL > property for that column. We don't implement anything about "known not > nullable", though, so I'd view this as a small part of an unimplemented > SQL feature. The usefulness seems rather debatable anyway. It is supposed to inspect the underlying column or look at the data values returned and set NOT NULL based on that? The later seems weird. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com
Bruce Momjian <bruce@momjian.us> writes:
> Tom Lane wrote:
>> Actually, SQL:2008 does say that if an output column of the SELECT is
>> known not nullable, then the created table should have the NOT NULL
>> property for that column. We don't implement anything about "known not
>> nullable", though, so I'd view this as a small part of an unimplemented
>> SQL feature. The usefulness seems rather debatable anyway.
> It is supposed to inspect the underlying column or look at the data
> values returned and set NOT NULL based on that? The later seems weird.
"Known not nullable" is entirely different from "all values happen to be
not null at the moment". I don't recall what conditions the SQL spec
expects people to be able to prove not-nullability from, but being a
direct copy of a NOT NULL column would certainly be the base case.
regards, tom lane