CODEWITHSUNDEEP
sqlpostgresql
Ela
Ela

Reputation: 123

Create a not null column with values from other column

I want to create a NOT NULL column in my table with values from an other column as default values. How I can do that?

ALTER TABLE mytable
    ADD COLUMN not_null_column TIME WITHOUT TIME ZONE NOT NULL DEFAULT old_column

but that not works because old_column is not constant

Any idea?

Upvotes: 1

Views: 1821

Answers (2)

Ela
Ela

Reputation: 123

I did something like that :

ALTER TABLE mytable
    ADD COLUMN not_null_column TIME WITHOUT TIME ZONE;

UPDATE mytable
SET not_null_column = old_column;


ALTER TABLE mytable
    ALTER COLUMN not_null_column SET NOT NULL;

Upvotes: 2

user330315
user330315

Reputation:

This is not possible.

The reason is explained in the manual where the DEFAULT clause in a CREATE TABLE is documented:

The DEFAULT clause assigns a default data value for the column whose column definition it appears within. The value is any variable-free expression (in particular, cross-references to other columns in the current table are not allowed).

(emphasis mine)

If you really need something like that, you will have to create a (before insert) trigger that populates not_null_column with the value of the other column.

Upvotes: 4

Related Questions