insert script with serials in postgres

Question

This is how I do this in sql server.

insert into users(name) values('jhon');
set @id = @@identity; -- last serial

insert into usersroles(@id, 1)
insert into usersroles(@id, 2)
insert into usersroles(@id, 3)

How can I do the same in postgres (withouth creating a function)?

Quassnoi · Accepted Answer · 2010-09-10 20:24:40Z

4

CREATE TABLE users (id SERIAL NOT NULL, name TEXT);
CREATE TABLE userroles (user_id INT NOT NULL, role_id INT NOT NULL);

INSERT
INTO    users (name)
VALUES  ('John');

INSERT
INTO    userroles (user_id, role_id)
VALUES  (CURRVAL('users_id_seq'), 1),
        (CURRVAL('users_id_seq'), 2),
        (CURRVAL('users_id_seq'), 3);

answered Sep 10, 2010 at 20:24

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

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

7 Comments

Omu Over a year ago

cool, but is there any other way like using declare or that's only inside plpgsql functions ?

Quassnoi Over a year ago

@Omu: DECLARE is only within PL/PGSQL. You can use unholy magic with custom_variable_classes to set session variables, but I wouldn't recommend this. Actually, what's bad in the CURRVAL solution?

Omu Over a year ago

@Quassnoi nothing, it's just my first day with postgres and I'm used with the sql server stuff

OMG Ponies Over a year ago

Is CURRVAL concurrency safe? Meaning, if another query called NEXTVAL('users_id_seq') between the INSERTS --wouldn't CURRVAL reflect the latest value and not the intended one?

Quassnoi Over a year ago

@OMGPonies: CURRVAL is implemented as a session variable. The queries called from another sessions do not affect CURRVAL. You have full control of the queries within your own session. This means that if you don't increment the sequence within your session, CURRVAL persists, even if another query in another session messes with the sequence. It's similar to SCOPE_IDENTITY in SQL Server.

|

Scott Bailey · Accepted Answer · 2010-09-10 21:12:43Z

1

Additionally you can use the keyword DEFAULT to specify the default value for the column. And the Postgres extension RETURNING to return the id that was just inserted.

INSERT INTO users (id, name) VALUES (default, 'Bob') RETURNING id;

answered Sep 10, 2010 at 21:12

Scott Bailey

8,3862 gold badges25 silver badges21 bronze badges

2 Comments

Quassnoi Over a year ago

how do you insert the returned value into another table without storing it into a variable first?

Scott Bailey Over a year ago

You don't. The point of using returning is to stuff it in a variable. If you don't want to use variables then you'll need to use currval()

Collectives™ on Stack Overflow

insert script with serials in postgres

2 Answers 2

7 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related