CREATE as SELECT with CONSTRAINT?

Question

I have a piece of SQL that creates a TABLE form a SELECT statement in PostgreSQL ; I want to add constraints to the table so that for example a column cannot be null.

I cannot find valid SQL for this in the Postgres Documentation, so it seems it is not possible.

What would be the best approach to achieve this kind of functionality? I would like creation of the table to fail if the constraint is violated.

I cannot create the table with the constraint beforehand because there might be some variation in some of the columns, as they are the result of a dynamic process.

Answer 1

A variation on @a_horse_with_no_name's answer would be to first create the table with the constraint and then insert into the table, still using a transaction, so everything rolls back in case of constraint violations. This is something you should consider if the rows to be inserted are a lot (i.e. in the hundreds of thousands or millions).

If it's difficult to create the create table statement dynamically, you could separate that part into 2 statements, with something like this:

begin transaction ;
    create table new_table                       -- create the table
    as
    select v.*
    from (select ....) as v                      -- as before
    where false ;                                -- but insert 0 rows

    alter table new_table add constraint ... ;   -- add the constraint

    insert into new_table                        -- and then attempt
    select ... ;                                 -- the insert
end ;

The difference with the other way is that the constraint will be there before any row is inserted into the table. And since NOT NULL and CHECK constraints are not deferrable but always checked immediately when a row is inserted or modified (and not at the end of the statement), your insert statement will fail (and rollback) with the first row that violates the constraint(s) you have placed.

If you are inserting multi-millions of rows, it will be faster, both because it may find an error more quickly but also because it will have to rollback less insertions.

Answer 2

You need to do that with two statements:

create table new_table
as
select ....;

alter table new_table add constraint ...;

Make sure you run this in a single transaction (e.g. by turning OFF auto commit in your SQL client, or wrapping that with a begin transaction ...

If the alter table fails, you can rollback everything and the table is gone as well.