Temp postgres table ignores "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 ;