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

Question

I'm adding a new, "NOT NULL" column to my Postgresql database using the following query (sanitized for the Internet):

ALTER TABLE mytable ADD COLUMN mycolumn character varying(50) NOT NULL;

Each time I run this query, I receive the following error message:

ERROR:  column "mycolumn" contains null values

I'm stumped. Where am I going wrong?

NOTE: I'm using pgAdmin III (1.8.4) primarily, but I received the same error when I ran the SQL from within Terminal.

Answer 1

Since rows already exist in the table, the ALTER statement is trying to insert NULL into the newly created column for all of the existing rows. You would have to add the column as allowing NULL, then fill the column with the values you want, and then set it to NOT NULL afterwards.

Answer 2

You either need to define a default, or do what Sean says and add it without the null constraint until you've filled it in on the existing rows.

Answer 3

Or, create a new table as temp with the extra column, copy the data to this new table while manipulating it as necessary to fill the non-nullable new column, and then swap the table via a two-step name change.

Yes, it is more complicated, but you may need to do it this way if you don't want a big UPDATE on a live table.

Answer 4

You have to set a default value.

ALTER TABLE mytable ADD COLUMN mycolumn character varying(50) NOT NULL DEFAULT 'foo';

... some work (set real values as you want)...

ALTER TABLE mytable ALTER COLUMN mycolumn DROP DEFAULT;

Answer 5

Specifying a default value would also work, assuming a default value is appropriate.

Answer 6

As others have observed, you must either create a nullable column or provide a DEFAULT value. If that isn't flexible enough (e.g. if you need the new value to be computed for each row individually somehow), you can use that fact that in PostgreSQL, all DDL commands can be executed inside a transaction:

BEGIN;
ALTER TABLE mytable ADD COLUMN mycolumn character varying(50);
UPDATE mytable SET mycolumn = timeofday();    -- Just a silly example
ALTER TABLE mytable ALTER COLUMN mycolumn SET NOT NULL;
COMMIT;