Use just inserted id in Postgres

Question

I want to create insert script for table Postgres database. The table should look like this.

  id    |    refid                  name
------------------------------------------------------------
autoinc |    null                |      admin
autoinc |    null                |      moderator
autoinc |    id of moderator     |      readonly
autoinc |    id of moderator     |      groupadmin
autoinc |    id of groupadmin    |      rolesadmin
autoinc |    id of rolesadmin    |      users
autoinc |    id of users         |      null

My problem is that I do not know how use just inserted id and re use it in the rest of the script. For example I want to insert row with moderator name and use moderator's id in 2 inserts below.

How can I achieve that?

I have tried with

WITH moderator AS (
    INSERT INTO table_name
        (refid, name)
    VALUES
        (null, 'moderator')
    RETURNING id
) 
INSERT INTO sd_roles (refid,rname)
VALUES   ((SELECT id FROM moderator), 'groupadmin')

But it is getting nested more and I would need to write with in with.

http://stackoverflow.com/questions/10471757/insert-rows-into-multiple-tables-in-a-single-query-selecting-from-an-involved-t — , Jun 24 '14 at 11:11
What exactly do you mean with "*But it is getting nested more*"? — , Jun 24 '14 at 11:34
@a_horse_with_no_name that I would have to write a with block for "groupadmin" insert and every inset who will be the reference — eomeroff, Jun 24 '14 at 11:39
Sorry I still don't understand. Can you post an example on what you want to do, that doesn't work with the current solution? Did you read Erwin's answer in the link I posted in my first comment? — , Jun 24 '14 at 11:41
@a_horse_with_no_name I have read the answer but not sure how it can help me. When I say nested, I think of inserting moderator in with and using its id to insert the group admin, but it does not stop, now I have to use gropuadmin to insert rolesadmin and roles admin to inset the user. — eomeroff, Jun 24 '14 at 11:44
You can combine multiple CTEs one after the other. Is that what you are looking for? — , Jun 24 '14 at 11:45
@a_horse_with_no_name sorry, I not sure what CTE is, I will research now. It might be what need. Can you please give me a push. — eomeroff, Jun 24 '14 at 11:49
a CTE is a common table expression: the `WITH` clause: http://www.postgresql.org/docs/current/static/queries-with.html — , Jun 24 '14 at 11:52
@a_horse_with_no_name thank you, but I still do not know how to reuse returned id from insert that follows with clause. — eomeroff, Jun 24 '14 at 12:09

score 1 · Answer 1 · answered Jun 25 '14 at 09:32

Instead of nesting, just chain them:

INSERT INTO table_name( refid, name )
VALUES( id, 'admin' );

INSERT INTO sd_roles( refid, name )
VALUES( null, 'moderator' )
RETURNING id;

INSERT INTO table_name( refid, name )
VALUES( id, 'readonly' ); -- using id of moderator

INSERT INTO table_name( refid, name )
VALUES( id, 'groupadmin' ) -- using id of readonly
RETURNING id;

INSERT INTO table_name( refid, name )
VALUES( id, 'rolesadmin' ) -- using id of groupadmin
RETURNING id;

INSERT INTO table_name( refid, name )
VALUES( id, 'users' ) -- using id of rolesadmin
RETURNING id;

INSERT INTO table_name( refid, name )
VALUES( id, null ); -- using id of users

score 0 · Answer 2 · answered Jun 24 '14 at 22:23

You can always define a variable and return the returning id inside it. However if you do not want to use variables. Then you can use lastval()

http://www.postgresql.org/docs/9.3/static/functions-sequence.html

"lastval: Return the value most recently returned by nextval in the current session. This function is identical to currval, except that instead of taking the sequence name as an argument it fetches the value of the last sequence used by nextval in the current session. It is an error to call lastval if nextval has not yet been called in the current session."

INSERT INTO table_name
    (refid, name)
VALUES
    (null, 'moderator');

INSERT INTO sd_roles 
    (refid,rname)
VALUES   
    (LASTVAL(), 'groupadmin')

score -3 · Answer 3 · answered Jun 24 '14 at 11:08

-3

You have to use SCOPE_IDENTITY()

DECLARE @id int
INSERT INTO table_name
        (refid, name)
    VALUES
        (null, 'moderator')

    SET @id =   SCOPE_IDENTITY()

    INSERT INTO sd_roles (refid,rname)
VALUES   (@id, 'groupadmin')

answered Jun 24 '14 at 11:08

P John Raj

537
1
4
17

2

that is not valid for Postgres – Jun 24 '14 at 11:10
This only support to MS SQl server – P John Raj Jun 24 '14 at 11:14
2

And the question is tagged with `postgresql`. So why would you answer with a solution that doesn't apply to the DBMS being used. – Jun 24 '14 at 11:22
Forgive me ,this link is help to you.http://stackoverflow.com/questions/2944297/postgresql-function-for-last-inserted-id and other one link http://stackoverflow.com/questions/241003/how-to-get-a-value-from-the-last-inserted-row – P John Raj Jun 24 '14 at 11:34
@PJohnRaj I am not looking for value from last inserted row but from row with specific condition. – eomeroff Jun 24 '14 at 11:46

Use just inserted id in Postgres

3 Answers3

Linked