Create a not null column with values from other column

Question

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?

"that not works because old_column is not constant" Define "not works". What should happen? What happens instead? What does it mean for a column not to be constant, and how does that related to a default value on another column? — underscore_d
– underscore_d, Commented Mar 31, 2021 at 13:44
I need to do a migration with flyway, but I can manage my db with Intellij's tool — Ela
– Ela, Commented Mar 31, 2021 at 13:44
@underscore_d constant is something like '2021-01-45' written in raw. It's not working means my migration can't be applied, an error pops — Ela
– Ela, Commented Mar 31, 2021 at 13:46
You could add the column them update it: ALTER TABLE mytable ADD COLUMN not_null_column TIME WITHOUT TIME ZONE NOT NULL DEFAULT NOW(); UPDATE mytable SET not_null_column=old_column; — Javier Ogando
– Javier Ogando, Commented Mar 31, 2021 at 14:39

user330315 · Accepted Answer · 2021-03-31 14:30:51Z

4

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.

answered Mar 31, 2021 at 14:30

user330315

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

Comments

Ela · Accepted Answer · 2021-03-31 15:29:57Z

2

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;

answered Mar 31, 2021 at 15:29

Ela

1232 silver badges10 bronze badges

Collectives™ on Stack Overflow

Create a not null column with values from other column

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related