3

I have a table that I want to add new not null varchar(255) column

My query is:

alter table poll_management.DASHLETS add column name varchar(255) not null;
update poll_management.DASHLETS as dashlet set name = report.name 
from poll_management.REPORTS as report
WHERE dashlet.id = report.reportdashletid

But I have an error:

ERROR:  column "name" contains null values
********** Error **********

ERROR: column "name" contains null values
SQL state: 23502
lospejos
  • 1,976
  • 3
  • 19
  • 35
Mr White
  • 97
  • 1
  • 9
  • either add column with null, then update values and alter to not null, or add column not null default 'somevalue'; – Vao Tsun Apr 30 '17 at 09:43
  • You have added "name" column as not null and as per the error it says that in report table some values of "name" column is null. – Abhishek B Patel Apr 30 '17 at 09:47
  • Does this answer your question? [How can I add a column that doesn't allow nulls in a Postgresql database?](https://stackoverflow.com/questions/512451/how-can-i-add-a-column-that-doesnt-allow-nulls-in-a-postgresql-database) – M Imam Pratama Oct 29 '22 at 02:43

2 Answers2

4

To avoid your error, two solutions come at once:

BEGIN;
  alter table poll_management.DASHLETS add column name varchar(255);
  update poll_management.DASHLETS as dashlet set name = report.name 
from poll_management.REPORTS as report;
  --mind I removed where, cos you need to update ALL rows to have some avlue
  alter table poll_management.DASHLETS alter column "name" set not null;
END;

and the other:

  alter table poll_management.DASHLETS add column name varchar(255) NOT NULL default 'not set';
Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
  • I think the query `alter table poll_management.DASHLETS alter column "name" not null;` must be changed to `SET not null` – radiospiel Jul 26 '18 at 08:43
0

i found that my query must be change to :

alter table poll_management.DASHLETS add column name varchar(255) not null DEFAULT 'value';
Mr White
  • 97
  • 1
  • 9