How to CREATE TABLE ... LIKE without the NOT NULL Constraints?
Try this:
CREATE TABLE my_table_bk
AS
SELECT *
FROM my_table
WHERE false;
Or, you can append LIMIT 0
instead of the WHERE
clause.
This will create a table my_table_bk
with the same structure as my_table
without constraints and without data.
This was asked on Stack Overflow in How to drop all NOT NULL constraints from a PostgreSQL table in one go. It appears to give a good range of solutions.
The accepted answer by Denis de Bernardy is:
You can group them all in the same alter statement:
alter table tbl alter col1 drop not null, alter col2 drop not null, …
You can also retrieve the list of relevant columns from the catalog, if you feel like writing a do block to generate the needed SQL. For instance, something like:
select a.attname from pg_catalog.pg_attribute a where attrelid = 'tbl'::regclass and a.attnum > 0 and not a.attisdropped and a.attnotnull;
(Note that this will include the primary key-related fields too, so you'll want to filter those out.)
If you do this, don't forget to use
quote_ident()
in the event you ever need to deal with potentially weird characters in column names.