How can I add a column that doesn't allow nulls in a Postgresql database?

sql

data-integrity

default-values

sql-queries

byNikita Barsukov·Sep 27, 2024

Here's the shortcut to add a non-null column with a default value (just to bypass those annoying NULL issues):

ALTER TABLE your_table ADD COLUMN new_col data_type NOT NULL DEFAULT 'temp_value';
ALTER TABLE your_table ALTER COLUMN new_col DROP DEFAULT;

This approach ensures that each row is provided with a value in the new column and then swiftly eliminates the default for any upcoming inserts.

Pre-flight checks before adding a NOT NULL column

When you set out to add a NOT NULL column, you need to ensure the new column receives a value for each row that already exists, adhering to the NON NULL constraint. Here are some actions you should consider ensuring the smooth sail of your operation.

Watch out for the existing data's null values

If your table is already filled with data, adding the non-null constraint above it might throw up an error, because the database can't promise that the new column for existing rows will comply with the constraint.

Proper sequence to handle existing data

Avoid this by initially adding the column without the NOT NULL constraint:

ALTER TABLE your_table ADD COLUMN new_col data_type;

Next step, distribute values to the new column:

UPDATE your_table SET new_col = 'desired_value' WHERE some_condition;

Once you are confident that all rows got their share of value, introduce the NOT NULL constraint:

ALTER TABLE your_table ALTER COLUMN new_col SET NOT NULL;

Don't forget about data integrity and transactions

You need a transaction to ensure data consistency since it allows either all modifications or none:

BEGIN;
-- Add NOT NULL column and fill it
COMMIT; -- or ROLLBACK, if someone screams "Abort! Abort!";

Common pitfalls and how to avoid them

While handling a NOT NULL column, things can go wrong, from data integrity to default value issues. So, here's your briefing on the mishaps that might occur and how to dodge them.

Interpreting the error

Seeing the error: "column 'mycolumn' contains null values"? That means you have some stubborn existing rows insisting on being NULL. Set them straight before applying the NOT NULL constraint.

Compliance to the column's data type

Your default value has to be in cahoots with the column’s data type. Any mismatch in this relationship can lead your SQL query to a rendezvous with failure.

Actions post-adding default

Once you've populated the column with the meaningful data, boot the default to avoid any accidental insertions of default data in the future.

Digging deeper - For the perfectionists out there

Slow and steady for live environments

For critical live environments, you might want to decouple the process. Start by adding the column without the NOT NULL constraint:

ALTER TABLE your_table ADD COLUMN new_col data_type;

Later, during a lull in activity, populate the new column and flip the NOT NULL switch:

ALTER TABLE your_table ALTER COLUMN new_col SET NOT NULL;

The clever conditional update

If the data to populate the new column requires a bit of thinking and varies conditionally, you can use UPDATE and CASE expressions to give it the brain it needs:

UPDATE your_table
SET new_col = CASE 
                 WHEN brainfreezer_condition1 THEN 'value1'
                 WHEN brainbuster_condition2 THEN 'value2'
                 ELSE 'default_value_for_dummies'
              END;