Postgres does not return lastval () correctly

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 (' [e-mail] ', 'Xpress', 'Care', 'f9fecdd84ee071806423adf30d6d6ff04e1a0a2c6688f2c057ddbab1d6b55d02', 4, 'EMQHTMMvViAB5BdYj0E6'); SELECT LASTVAL(); 

LASTVAL always returns 39037, which should be technically equal to 838. It also does not insert it into the database for some reason. I googled and searched for everything that I can think of and do not get any answers. Does anyone know what is going on here?

Answer 1

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

You should completely avoid lastval . Using:

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

where id follows the name of the generated key column.

This approach will handle multi-valued inserts and INSERT INTO ... SELECT ... correctly, and there will be no problems with sequence triggers.

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