Postgres没有正确返回lastval（） [英] Postgres not returning lastval() properly
问题描述
我正在尝试通过CLI中的psql将新用户插入我们的数据库。当我执行以下操作时：
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 始终返回39037，从技术上讲应该如此是838。由于某种原因，它也没有将其插入数据库。我已经在Google上搜索并寻找了我能想到的所有内容，但没有得到任何答案。有人知道这里发生了什么吗？
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?
推荐答案
这里的简短版本是使用不合格的 lastval 是个坏主意。触发器，规则等可能会引起问题。
The short version here is that using unqualified lastval is a bad idea. Triggers, rules, etc can cause problems.
您应该完全避免使用 lastval 。使用：
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;
其中 id 应以
此方法将处理多值插入和 INSERT INTO ... SELECT ...
This approach will handle multi-valued inserts and INSERT INTO ... SELECT ... correctly, and won't have issues with triggers touching sequences.
如果必须使用基于函数调用的方法，至少应使用 currval（ 'tablename_id_seq'）（传递适当的序列名），而不是 lastval 。
If you must use a function-call based approach, at least use currval('tablename_id_seq') (passing the appropriate sequence name) instead of lastval.
这篇关于Postgres没有正确返回lastval（）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
