How to alter new not null column in Postgresql?

Question

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

either add column with null, then update values and alter to not null, or add column not null default 'somevalue'; — Vao Tsun
– Vao Tsun, Commented Apr 30, 2017 at 9: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
– Abhishek B Patel, Commented Apr 30, 2017 at 9:47
Does this answer your question? How can I add a column that doesn't allow nulls in a Postgresql database? — M Imam Pratama
– M Imam Pratama, Commented Oct 29, 2022 at 2:43

Vao Tsun · Accepted Answer · 2018-07-26 11:26:44Z

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

edited Jul 26, 2018 at 11:26

answered Apr 30, 2017 at 9:46

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

radiospiel Over a year ago

I think the query alter table poll_management.DASHLETS alter column "name" not null; must be changed to SET not null

Mr White · Accepted Answer · 2017-04-30 09:46:50Z

0

i found that my query must be change to :

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

answered Apr 30, 2017 at 9:46

Mr White

1441 silver badge10 bronze badges

Collectives™ on Stack Overflow

How to alter new not null column in Postgresql?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related