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?

Answer 1

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.