Postgresql – How to CREATE TABLE … LIKE without the NOT NULL Constraints

postgresql

I'm trying recreate a table's structure inside a function by using some dynamic SQL.

EXECUTE 'CREATE TABLE ' || my_table_name || '_bk' ||
    ' (like ' || _my_table_name || ')';

That will be similar to: 

CREATE TABLE my_table_bk
(like my_table);

But I need to discard all constraints. Using EXCLUDING CONSTRAINTS in the Like Options, it still copy the NOT NULL Constraints (Documentation confirms this behavior):

CREATE TABLE my_table_bk
(like my_table EXCLUDING CONSTRAINTS);

The question is, how can I recreate the table structure without the NOT NULL constraints, or, in alternative, remove all NOT NULL constraints in a table?

Best Answer

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.

Related Question