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

sql postgresql constraints sql-drop notnull
Stefan picture Stefan · Nov 22, 2013 · Viewed 57k times · Source

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.

Answer

Denis de Bernardy picture Denis de Bernardy · Nov 22, 2013

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 questions

Postgres: Add constraint if it doesn't already exist

Does Postgres have any way to say ALTER TABLE foo ADD CONSTRAINT bar ... which will just ignore the command if the constraint already exists, so that it doesn't raise an error?

Read More
Adding named foreign key constraints in a SQL Create statement

I currently have: CREATE TABLE galleries_gallery ( id INT NOT NULL PRIMARY KEY IDENTITY, title NVARCHAR(50) UNIQUE NOT NULL, description VARCHAR(256), templateID INT NOT NULL REFERENCES galleries_templates(id), jsAltImgID INT NOT NULL REFERENCES libraryImage(id) jsAltText NVARCHAR(500), dateCreated SMALLDATETIME …

Read More
How to get the name of a unique constraint in postgresql?

I need to drop a unique constraint from a postgresql table, but I didn't give it a name in the schema. Does anybody know, how to get the name of such a constraint, or how to drop it?

Read More