How to use RETURNING clause in PostgreSQL?

Question

I'm using PostgreSQL 8.2 (it should be the minimum version supporting RETURNING), precisely 8.2.19 on GNU/Linux. I tried to use returning clause on automatically inserted column that is also constrained as primary key. The insertion of new row is correctly completed but the returned result is empty (verified with query executer in pgAdmin). I tried implicit insertion and also specifying DEFAULT for primary key column.

The column I tried to return is defined as

al_id integer NOT NULL DEFAULT nextval(('"allarmi_al_id_seq"'::text)::regclass)

and the insertion query is like

INSERT INTO alarms (al_id, al_descr) 
     VALUES (DEFAULT, 'description') 
  RETURNING al_id;

allarmi_al_id_seq is obviously (?) a sequence and is defined as

CREATE SEQUENCE allarmi_al_id_seq
    INCREMENT 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    START 34564230
    CACHE 1;

What's wrong?

looks like it's working in 8.3.20 - sqlfiddle.com/#!10/309b9/1. Should be some fix between these 2 versions :) — roman
– roman, Commented Sep 9, 2013 at 9:24
Did you try it without expliciting the al_id? sqlfiddle.com/#!10/309b9/4 — Clodoaldo Neto
– Clodoaldo Neto, Commented Sep 9, 2013 at 11:31
Yes, I was referring exactly to that style saying "implicit insertion" — Ufo.rob
– Ufo.rob, Commented Sep 10, 2013 at 12:41
Does it work INSERT INTO alarms (al_descr) VALUES ('description') RETURNING al_id; ? — Luc M
– Luc M, Commented Jan 27, 2014 at 20:08

krisku · Accepted Answer · 2013-09-09 11:31:10Z

2

You can also explicitly read the current value of the sequence and return that:

INSERT INTO alarms (al_id, al_descr) 
     VALUES (DEFAULT, 'description') 
  RETURNING currval('allarmi_al_id_seq') AS al_id;

Note that the currval function returns the most recently obtained value in this session, so there are no concurrency issues (unless you have a trigger that causes the sequence to be incremented).

answered Sep 9, 2013 at 11:31

krisku

3,9911 gold badge21 silver badges11 bronze badges

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

Collectives™ on Stack Overflow

How to use RETURNING clause in PostgreSQL?

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