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

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.

ALTER TABLE table_name ALTER COLUMN [SET NOT NULL| DROP NOT NULL]

If you want to drop all NOT NULL constraints in PostreSQL you can use this function:

CREATE OR REPLACE FUNCTION dropNull(varchar) RETURNS integer AS $$DECLARE  columnName varchar(50);BEGIN    FOR columnName IN  select a.attname  from pg_catalog.pg_attribute a where attrelid = $1::regclass   and a.attnum > 0   and not a.attisdropped   and a.attnotnull and a.attname not in(   SELECT                 pg_attribute.attnameFROM pg_index, pg_class, pg_attribute WHERE   pg_class.oid = $1::regclass AND  indrelid = pg_class.oid AND  pg_attribute.attrelid = pg_class.oid AND   pg_attribute.attnum = any(pg_index.indkey)  AND indisprimary)          LOOP          EXECUTE 'ALTER TABLE ' || $1 ||' ALTER COLUMN '||columnName||' DROP NOT NULL';                END LOOP;    RAISE NOTICE 'Done removing the NOT NULL Constraints for TABLE: %', $1;    RETURN 1;END;$$ LANGUAGE plpgsql;

Please note that the primary keys will be excluded.

Then you can call it using:

SELECT dropNull(TABLENAME);