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

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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

this query will auto-update the nulls   ALTER TABLE mytable ADD COLUMN mycolumn character varying 50  DEFAULT  whatever  NOT NULL

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

This worked for me      ALTER TABLE your table name ADD COLUMN new column name int

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

This worked for me      ALTER TABLE your table name ADD COLUMN new column name int

User · Answer

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

User · Answer

this query will auto-update the nulls   ALTER TABLE mytable ADD COLUMN mycolumn character varying 50  DEFAULT  whatever  NOT NULL

User · Answer

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

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

Examples related to sql

Examples related to postgresql

Examples related to alter-table