CODEWITHSUNDEEP
sqlpostgresqlsql-insertinsert-query
riccardo.tasso
riccardo.tasso

Reputation: 1023

how can I reference another column inside postgres insert query?

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?

Upvotes: 0

Views: 2555

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

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.

Upvotes: 1

Popeye
Popeye

Reputation: 35930

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.

Upvotes: 0

Related Questions