How to CREATE TABLE ... LIKE without the NOT NULL Constraints?

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

Update: this method is better if you want to duplicate the table structure without constraints

I think the easiest way to create a table like without NOT NULL constraints, is to use a simple pg_dump and sed

pg_dump -Ox --schema-only -t myTable myDatabase |
  sed -ne'/^CREATE TABLE/,/);/p' -e's/^NOT NULL//';

You can easily expand this method to nuke out the rest of the constraints. That term is kind of broad, so I'm not sure what constraints you're not wanting.

Options

The options can be found with pg-dump --help,

-O, --no-owner         skip restoration of object ownership in
-x, --no-privileges    do not dump privileges (grant/revoke)