CODEWITHSUNDEEP
sqlpostgresqlsql-null
Mr White
Mr White

Reputation: 144

How to alter new not null column in Postgresql?

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

Upvotes: 3

Views: 6585

Answers (2)

Vao Tsun
Vao Tsun

Reputation: 51406

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';

Upvotes: 4

Mr White
Mr White

Reputation: 144

i found that my query must be change to :

alter table poll_management.DASHLETS add column name varchar(255) not null DEFAULT 'value';

Upvotes: 0

Related Questions