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, Jan 19 '21 at 10:49

score 1 · Accepted Answer · answered Jan 19 '21 at 11:50

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.

score 0 · Answer 2 · answered Jan 19 '21 at 10:50

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 '21 at 10:50

Popeye

35,427
4
10
31

how can I reference another column inside postgres insert query?

2 Answers2