336

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.

MMalke
  • 1,857
  • 1
  • 24
  • 35
Huuuze
  • 15,528
  • 25
  • 72
  • 91

8 Answers8

516

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;
Luc M
  • 16,630
  • 26
  • 74
  • 89
  • 7
    @SeanBright , you can access to postgres doc offline by doing `man ALTER_TABLE` :) – allan.simon Oct 18 '15 at 19:49
  • 4
    To clarify: the default value is only needed to update existing rows, which is why it can be dropped immediately afterwards. All existing rows have been updated when the table was altered (which may take some time, obviously) – MSalters Apr 26 '16 at 10:14
  • 4
    This won't work if you want to use another column to compute the initial value for existing rows. [j_random_hacker's answer](http://stackoverflow.com/a/516016/1394393) allows for that, making it more robust. – jpmc26 May 05 '16 at 23:51
  • It works but the accepted answer IMO should be @j_random_hacker. Firstly for the reason described above, secondly it's imo abusing default values since the goal never is to set a default value and we don't really need to. The goal is to set values and have it not nullable in which case the other answer is strictly better. PS, I've seen devs use this advice and forget the DROP Default, I'd prefer they forget the NOT NULL in that case instead of suddenly having an ID 1 with thousands of relations towards later on when bugs arise :) – Brecht De Rooms Apr 17 '23 at 10:42
123

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 the 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;
j_random_hacker
  • 50,331
  • 10
  • 105
  • 169
  • 13
    even in a transaction, NOT NULL is enforced immediately, so must first add column, fill values, then add NOT NULL — as this answer does. (tested on postgres 9.6) – Beni Cherniavsky-Paskin Oct 29 '17 at 09:44
55

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.

Sean Bright
  • 118,630
  • 17
  • 138
  • 146
6

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.

Paul Tomblin
  • 179,021
  • 58
  • 319
  • 408
5

this query will auto-update the nulls 

ALTER TABLE mytable ADD COLUMN mycolumn character varying(50) DEFAULT 'whatever' NOT NULL;
jacktrade
  • 3,125
  • 2
  • 36
  • 50
3

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

Ryann Graham
  • 8,079
  • 2
  • 29
  • 32
  • 5
    It would improve the answer to give the amended syntax to create the column with a default value (for illustration). – hardmath Sep 03 '15 at 01:39
-1

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.

alphadogg
  • 12,762
  • 9
  • 54
  • 88
  • 4
    I didn't -1 you, but I think there may be subtle difficulties with this -- e.g. I'm betting that existing indices, triggers and views will continue to refer to the original table even after the rename as I think they store the relid of the table (which doesn't change) rather than its name. – j_random_hacker Feb 05 '09 at 13:56
  • 1
    Yes, I should have stated that the new table should be an exact copy of the original, including adding indices and such. My bad for being too brief. The reason for this is that there are also subtle diffculties of performing an ALTER on a table that may be live, and sometimes you need to stage it. – alphadogg Feb 05 '09 at 14:23
  • For example, using the DEFAULT approach, you'll add that default value to each row. Not sure how Postgres locks up a table when doing this. Or, if column order matters, you can't just add a column with the ALTER command. – alphadogg Feb 05 '09 at 14:31
  • 1
    Fair enough, ALTER TABLE does lock the table according to the PostgreSQL docs, however your non-transactional approach risks losing changes if the table is indeed live. Also I'd suggest that any code that relies on column order is broken (that may be outside your control of course). – j_random_hacker Feb 07 '09 at 01:51
  • 2
    This approach is also particularly problematic if the table is referenced by foreign key indexes as those would all have to be recreated as well. – Aryeh Leib Taurog Nov 24 '15 at 13:54
-17

This worked for me: :)

ALTER TABLE your_table_name ADD COLUMN new_column_name int;
Tim Siwula
  • 966
  • 11
  • 15