MacLochlainns Weblog

You can’t disable a not null constraint in Postgres, like you can do in Oracle. However, you can remove the not null constraint from a column and then re-add it to the column.

Here’s a quick test case in four steps:

1. Drop a demo table if it exists:

   DROP TABLE IF EXISTS demo;

   DROP TABLE IF EXISTS demo;

2. Create a demo table if it exists:

   CREATE TABLE demo ( demo_id SERIAL , demo_text VARCHAR(20) NOT NULL );

   CREATE TABLE demo ( demo_id SERIAL , demo_text VARCHAR(20) NOT NULL );

3. Insert a compliant row in the demo table if it exists:

   INSERT INTO demo (demo_text) VALUES ('Thing 1');

   INSERT INTO demo (demo_text) VALUES ('Thing 1');

   Attempt to insert another row with a null value in the demo_text column:

   INSERT INTO demo (demo_text) VALUES (NULL);

   INSERT INTO demo (demo_text) VALUES (null);

   It raises the following error:

   INSERT 0 1 psql:remove_not_null.sql:22: ERROR: NULL VALUE IN COLUMN "demo_text" violates not-NULL CONSTRAINT DETAIL: Failing ROW contains (2, NULL).

   INSERT 0 1 psql:remove_not_null.sql:22: ERROR: null value in column "demo_text" violates not-null constraint DETAIL: Failing row contains (2, null).

4. You can drop the not null constraint from the demo_text column:

   ALTER TABLE demo ALTER COLUMN demo_text DROP NOT NULL;

   ALTER TABLE demo ALTER COLUMN demo_text DROP NOT NULL;

   You can now successfully insert a row with a demo_text column value of null. After you have performed your table maintenance you can add the not null constraint back on to the demo_text column.

   You need to update the row with a null value in the demo_text column with a valid value before you re-add the not null constraint. The following shows an update statement that replaces the null value with a text string:

   UPDATE demo SET demo_text = 'Thing 2' WHERE demo_text IS NULL;

   UPDATE demo SET demo_text = 'Thing 2' WHERE demo_text IS NULL;

   Now, you can change the demo_text column back to a not null constrained column with the following syntax.

   ALTER TABLE demo ALTER COLUMN demo_text SET NOT NULL;

   ALTER TABLE demo ALTER COLUMN demo_text SET NOT NULL;

While you can not defer the constraint, removing it and adding it back works well.