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.
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.attname
FROM 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);