Re: CTAS not honoring NOT NULL, DEFAULT modifiers

On Mon, Apr 19, 2010 at 08:32, Nikhil Sontakke
<nikhil(dot)sontakke(at)enterprisedb(dot)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/