How can I add a column that doesn't allow nulls in a Postgresql database?
SqlPostgresqlAlter TableSql Problem Overview
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.
Sql Solutions
Solution 1 - Sql
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;
Solution 2 - Sql
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;
Solution 3 - Sql
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.
Solution 4 - Sql
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.
Solution 5 - Sql
Specifying a default value would also work, assuming a default value is appropriate.
Solution 6 - Sql
this query will auto-update the nulls
ALTER TABLE mytable ADD COLUMN mycolumn character varying(50) DEFAULT 'whatever' NOT NULL;
Solution 7 - Sql
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.
Solution 8 - Sql
This worked for me: :)
ALTER TABLE your_table_name ADD COLUMN new_column_name int;