How to insert and then update returned Id from insert query as returning id in a single command in postgres?

Question

I have a demo table

CREATE TABLE items (
  id SERIAL primary key,
  user_id integer,
  name character varying,
  created timestamp with time zone default now()
);

And I want a single query to run and first insert data, then return primary key using returning id and then update the same table with the returned id.

INSERT INTO items (name) values ('pen') RETURNING id as idd 
update items set user_id=(select idd) where id=(select idd)

but the above command doesn't work and throws syntax error. Any help will be appriciated.

I am not changing the primary key, I just want to insert primary key that I got in user_id column. — Sanyam Madaan
– Sanyam Madaan, Commented Feb 21, 2020 at 5:41
What about adding a trigger to items table that sets column user_id to the generated id value, instead of performing an UPDATE immediately after an INSERT? In any case, why do you store the ID twice in the same table? Doesn't that contradict normalization? — Abra
– Abra, Commented Feb 21, 2020 at 5:52
Have a particular requirement as this will not be true in every case. I am creating some sort of tree structure in database. Discussed about the trigger with my senior but they said it will unnecessarily complex the table and database structure — Sanyam Madaan
– Sanyam Madaan, Commented Feb 21, 2020 at 5:54
If the database trigger is not an option, what about a PL/pgSQL routine that performs both the INSERT and the subsequent UPDATE? Or does that also make the structure unnecessarily complex? — Abra
– Abra, Commented Feb 21, 2020 at 6:03

user330315 · Accepted Answer · 2020-02-21 07:06:04Z

1

You can do that right within the INSERT statement:

INSERT INTO items 
  (name, user_id) 
values 
  ('pen', currval(pg_get_serial_sequence('items','id')));

Online example

answered Feb 21, 2020 at 7:06

user330315

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

2 Comments

Sanyam Madaan Over a year ago

Thanks it worked. So instead of currval can returning Id be used if so how :)

user330315 Over a year ago

@SanyamMadaan: no, you can not refer to other columns in the values clause

Harshit Shah · Accepted Answer · 2020-02-21 08:29:36Z

0

You can try this way also :

create temp table insert_item as 
with insert_item_cte as ( 
INSERT INTO items (name) 
 values ('pen') returning id 
) 
select id from insert_item_cte;

update items set user_id = items.id 
from insert_item ii 
where ii.id = items.id;

Online Demo

answered Feb 21, 2020 at 8:29

Harshit Shah

3191 gold badge2 silver badges11 bronze badges

Collectives™ on Stack Overflow

How to insert and then update returned Id from insert query as returning id in a single command in postgres?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related