how can I reference another column inside postgres insert query?

Question

I have the following table:

CREATE TABLE tab (
    id SERIAL PRIMARY KEY,
    code TEXT NOT NULL,
    data TEXT
)

In some cases, I'd like to insert a new row ensuring that the code column is generated by the id column. In other cases the code is provided by the user.

For example:

INSERT INTO tab(code, data) VALUES ('code: ' || id::TEXT, 'my data');

The expected result is something like:

id	code	data
1	code: 1	abc
2	code: 2	xyz


INSERT INTO tab(code, data) VALUES ('user.provided.code', 'my data');

The expected result is something like:

id	code	data
1	code: 1	abc
2	code: 2	xyz
3	user.provided.code	xyz

Is it possibile in one statement?

Don't do it. You would be exposing the PK value. PK's job only is to be unique. Use a secondary generated key if you want to expose it somewhere (UI, external app, client, etc.) — The Impaler
– The Impaler, Commented Jan 19, 2021 at 10:49

Gordon Linoff · Accepted Answer · 2021-01-19 11:50:23Z

1

It sounds like you want to default the coder to something based on the id. Unfortunately, this doesn't work in Postgres:

create table tab (
    id   integer primary key generated always as identity,
    code text not null default ('code '||id::text),
    data text
);

One option is a single statement that does both an insert and update:

with i as (
      insert into tab (code, data)
           values ('', 'my data'
           returning *
     )
update tab
    set code = 'code: ' || id::TEXT
    where tab.id in (select i.id from i);

Another is to use a trigger that assigns the value.

answered Jan 19, 2021 at 11:50

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Popeye · Accepted Answer · 2021-01-19 10:50:31Z

0

Use INSERT INTO .. SELECT as follows:

INSERT INTO tab(code, data) 
select 'code: ' || id::TEXT, 'my data' from tab;

Note: In newly added data(above insert), you are missing to add id column data or I think it is auto generated.

answered Jan 19, 2021 at 10:50

Popeye

36k4 gold badges12 silver badges31 bronze badges

Collectives™ on Stack Overflow

how can I reference another column inside postgres insert query?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related