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

Question

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.

Sibell

Try seeing i these links can help you figure out a way to implement this. #3370659 #2541115 – Swish

Answer 1

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.

Semiotics

Your query is great. I've never queried the catalog. Giving the DO block a try will be my next step. – Sibell

Answer 2

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

Dasi

Answer 3

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);

Uri

Answer 4

Preliminaries

Some columns may be required to stay NOT NULL by constraints. The only standard case being PRIMARY KEY column(s). You may have additional custom constraints. Can be done by checking the system catalog pg_constraint. See below.

Also note: the code examples below work up to Postgres 15, but changes to system catalogs in future releases may invalidate it (even if unlikely).

Proper way

The clean way only needs regular privileges to ALTER the table. You can automate with dynamic SQL in a DO statement (implementing what Denis already suggested):

DO
$do$
DECLARE
   _tbl regclass := 'tbl_b';  -- your table here, schema-qualify if needed!
BEGIN
   RAISE NOTICE '%', (  -- debug mode
   -- EXECUTE (         -- uncomment to automate payload     
      SELECT concat_ws(' '
                     , 'ALTER TABLE'
                     , _tbl
                     , 'ALTER'
                     , string_agg (quote_ident(attname), ' DROP NOT NULL, ALTER ')
                     , 'DROP NOT NULL'
                      )
      FROM   pg_catalog.pg_attribute a
      WHERE  attrelid = _tbl
      AND    attnotnull             -- only NOT NULL columns
      AND    NOT attisdropped       -- exclude dropped columns
      AND    attnum > 0             -- exclude system columns
      AND    NOT EXISTS (           -- exclude PK columns
         SELECT FROM pg_constraint c
         WHERE  c.conrelid = a.attrelid
         AND    c.contype  = 'p'    -- PRIMARY KEY
         AND    a.attnum   = ANY (c.conkey)
         )
      );
END
$do$;

Very fast. Does not trigger a table rewrite in modern versions of Postgres.

Execute care with dynamic commands and be wary of SQL injection. I switched this to debug mode, so it only reports the DDL code as NOTICE. Have a look.
If your confident, comment the RAISE NOTICE line and un-comment EXECUTE to prime the bomb.

This is a spin-off from this bigger answer:

• Generate DEFAULT values in a CTE UPSERT using PostgreSQL 9.3

Over there I drop NOT NULL constraints from a table created with:

CREATE TABLE tbl_b (LIKE tbl_a INCLUDING DEFAULTS);

Since, per documentation:

Not-null constraints are always copied to the new table.

Quick and dirty

There is a quick and dirty way with superuser privileges:

UPDATE pg_attribute
SET    attnotnull = FALSE
WHERE  attrelid = 'tbl_b'::regclass  -- schema-qualify if needed!
AND    attnotnull          -- only NOT NULL columns
AND    NOT attisdropped    -- exclude dropped columns
AND    attnum > 0          -- exclude system columns
AND    NOT EXISTS (        -- exclude PK columns
   SELECT FROM pg_constraint c
   WHERE  c.conrelid = a.attrelid
   AND    c.contype  = 'p'  -- PRIMARY KEY
   AND    a.attnum   = ANY (c.conkey)
   );

The shortcut is tempting. But screw this up, and you may break your database (cluster). The basic rule is: never tamper with system catalogs directly. So not recommended.

Saxecoburggotha

The DO block snippet doesn't work because it also contains primary keys that pg refuses to drop. – Illogicality

@user: The OP asked for "all NOT NULL constraints". Still, to make the answer more universally useful I updated to exempt PK columns. – Saxecoburggotha

Answer 5

I had a scenario needing to remove the NOT NULL from every field with a certain name across the entire database. Here was my solution. The where clause could be modified to handle whatever search pattern you need.

DO $$ DECLARE row record;
BEGIN FOR row IN 
    (
        SELECT
            table_schema, table_name, column_name
        FROM
            information_schema.columns 
        WHERE
            column_name IN ( 'field1', 'field2' )
    )
    LOOP
        EXECUTE 
          'ALTER TABLE ' || row.table_schema || '.' || row.table_name || ' ALTER '
       || string_agg (quote_ident(row.column_name), ' DROP NOT NULL, ALTER ')
       || ' DROP NOT NULL;';
    END LOOP;
END; $$;

piggybacked off some other examples, this worked better for my needs

Carisa

Answer 6

Yes, it is. I had the same issue..

To resolve, I had to write a C# .net script which traversed all the plSql database and removed all the matching constraints..

For, specific info on how to remove single constraints, pl follow the link. http://www.techonthenet.com/oracle/foreign_keys/drop.php

Harrisharrisburg