Postgresql – Postgres not returning lastval() properly

postgresqlsequence

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 ('xpress@carepilot.com', '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?

Best Answer

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 ('xpress@carepilot.com', '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.

Related Topic