Postgresql copy generated column

Question

Let's assume a table t:

create table if not exists t(
    a integer primary key generated by default as identity,
    b integer,
    c text
);

and simple insert insert into t (b, c) values (2, 'abc'); Is there any option how to rewrite this insert and as a result in column b will be coalesce(b, a)? So when b is null, b equals to generated column a.

I know there is an option to use RETURNING keyword and update or to use before insert trigger.

No:insert into t (b, c) values (coalesce(null, a), 'abc'); HINT: There is a column named "a" in table "t", but it cannot be referenced from this part of the query. — Adrian Klaver
– Adrian Klaver, Commented Feb 10, 2021 at 20:40

Laurenz Albe · Accepted Answer · 2021-02-11 01:47:01Z

1

What you intend to do introduces redundancy, so I recommend that you don't do it. Rather, use coalesce wjen you query the table. A convenient solution would be a view on the table that does that.

If you insist on persisting that in the table, you will have to use a trigger.

answered Feb 11, 2021 at 1:47

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

Postgresql copy generated column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related