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
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
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.