sqlpostgresqlconstraintssql-dropnotnull

How to drop all NOT NULL constraints from a PostgreSQL table in one go


Is it possible to drop all NOT NULL constraints from a table in one go?

I have a big table with a lot of NOT NULL constraints and I'm searching for a solution that is faster than dropping them separately.


Solution

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