Temp postgres table ignores "excluding constraints"

In PostgreSQL 9.5.

I'd like to create a temporary copy of my table without its primary key. Postgres has a feature { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | ALL } - which I'm attempting to use.

create table test (
    col1    integer,
    col2    integer,
    col3    text,
    primary key (col1)
);

create temporary table t (like test excluding constraints);

insert into t (col1, col2, col3) values (1, 2, 'foo'), (null, 2, 'bar');

If I'm understanding excluding constraints correctly, that should not bring over the non-null constraint on test to t.

But here's the error that I receive:

ERROR:  null value in column "col1" violates not-null constraint
DETAIL:  Failing row contains (null, 2, bar).
********** Error **********

ERROR: null value in column "col1" violates not-null constraint
SQL state: 23502
Detail: Failing row contains (null, 2, bar).

I have a workaround, create temporary table t as select * from test where true = false, but I'd like to know what I'm not understanding correctly about excluding constraints.

I'd like to know what I'm not understanding correctly about excluding constraints.

What is happening here is that primary key (col1) creates a primary key constraint but doesn't create a separate not null constraint. It adds a not null "modifier" to the column. This works as a constraint, ie. no nulls are allowed in col1 but it is not a named constraint. That's why it's not removed by the excluding constraints, it's a column property.

As the CREATE TABLE documentation states:


Not-null constraints are always copied to the new table. CHECK constraints will be copied only if INCLUDING CONSTRAINTS is specified. ..

If you had the table defined with an explicit CHECK column is not null constraint instead:

create table test (
    col1    integer,
    col2    integer,
    col3    text,
    unique (col1),
    check (col1 is not null)
);

then the excluding constraints would have succeeded. But if the table has a primary key defined, then all the columns of this primary key are created with the not null modifier and a create .. like test will copy these modifiers.

Your workaround is valid. Either of these will work:

create temporary table t as select * from test where false ;
create temporary table t as select * from test limit 0 ;

StackExchange Database Administrators Q#141728, answer score: 3