Postgres not returning lastval() properly

Question

I am trying to insert a new user into our database, via psql in the CLI. When I do the following:

START TRANSACTION;
INSERT INTO "users" ("email", "first_name", "last_name", "password", "objectstate_id", "activate_rid")
VALUES ('[email protected]', 'Xpress', 'Care', 'f9fecdd84ee071806423adf30d6d6ff04e1a0a2c6688f2c057ddbab1d6b55d02', 4, 'EMQHTMMvViAB5BdYj0E6');
SELECT LASTVAL();

LASTVAL always returns 39037, which should technically be 838. It is also not inserting it into the DB for some reason. I have googled and looked for everything that I can think of and am not getting any answers. Does anyone have any idea what is going on here?

Do you have any triggers or rules defined on the users table? One of those may be causing the insert to fail. That could also be doing another call of nextval() and thus cause your use of lastval() to give you an unexpected answer. — qqx
– qqx, Commented Jul 23, 2013 at 19:44

Craig Ringer · Accepted Answer · 2013-07-24 02:55:20Z

11

The short version here is that using unqualified lastval is a bad idea. Triggers, rules, etc can cause problems.

You should avoid lastval entirely. Use:

BEGIN;

INSERT INTO "users" ("email", "first_name", "last_name", "password", "objectstate_id", "activate_rid") 
VALUES ('[email protected]', 'Xpress', 'Care', 'f9fecdd84ee071806423adf30d6d6ff04e1a0a2c6688f2c057ddbab1d6b55d02', 4, 'EMQHTMMvViAB5BdYj0E6') 
RETURNING id;

where id should by the name of the generated key column.

This approach will handle multi-valued inserts and INSERT INTO ... SELECT ... correctly, and won't have issues with triggers touching sequences.

If you must use a function-call based approach, at least use currval('tablename_id_seq') (passing the appropriate sequence name) instead of lastval.

answered Jul 24, 2013 at 2:55

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

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

Collectives™ on Stack Overflow

Postgres not returning lastval() properly

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related