Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 949
Stefan Avatar asked Nov 22 '13 13:11

Stefan


People also ask

How do you remove NOT NULL constraints in a table?

To remove a NOT NULL constraint for a column in MySQL, you use the ALTER TABLE .... MODIFY command and restate the column definition, removing the NOT NULL attribute.

How do I get rid of NOT NULL in PostgreSQL?

To remove the NOT NULL constraint, use the DROP NOT NULL clause along with ALTER TABLE ALTER COLUMN statement.

Can we drop NOT NULL constraint existing table?

We can remove a NOT NULL constraint from a column of an existing table by using the ALTER TABLE statement.

How do I drop NOT NULL constraint in SQL Server?

To remove a NOT NULL constraint for a column in SQL Server, you use the ALTER TABLE .... ALTER COLUMN command and restate the column definition.


1 Answers

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.

like image 79
Denis de Bernardy Avatar answered Sep 18 '22 09:09

Denis de Bernardy