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

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?

3 Answers 3


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.


Try this:

CREATE TABLE my_table_bk
  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 is definitely cleaner than accepted answer for the OP's use case; about the only advantage that the accepted has is that it still allows using the create table .. (like ..) and potentially INCLUDING other options. Otherwise this seems preferable.
    – kevlarr
    Oct 30, 2020 at 16:26
  • Is there anyway to do this and add additional columns to the new table? Obviously can just do an ALTER TABLE and add the columns right after, but I'm wondering if there's a more direct way. Mar 3, 2021 at 22:12

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.


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)
  • 1
    I'm a big fan of using sed (and even awk - if I can ever master it!) for this kind of task - it's relatively easy to construct nice pipelines. Just as a matter of interest, what does the -0x parameter do with pg_dump? (+1)!
    – Vérace
    Dec 1, 2020 at 4:47
  • @Vérace updated Dec 1, 2020 at 5:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.