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)?

Answer 1

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);

Answer 2

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;