postgresql 使用主键序列在 postgres 表上手动插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3905378/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Manual inserts on a postgres table with a primary key sequence
提问by Michael Clerx
I'm converting a MySQL table to PostgreSQL for the first time in my life and running into the traditional newbie problem of having no auto_increment.
我有生以来第一次将 MySQL 表转换为 PostgreSQL，并遇到了没有 auto_increment 的传统新手问题。
Now I've found out that the postgres solution is to use a sequence and then request the nextval() of this sequence as the default value every time you insert. I've also read that the SERIAL type creates a sequence and a primary key automatically, and that nextval() increments the counter even when called inside transactions to avoid locking the sequence.
现在我发现 postgres 的解决方案是使用一个序列，然后在每次插入时请求这个序列的 nextval() 作为默认值。我还读到 SERIAL 类型会自动创建一个序列和一个主键，并且即使在事务内部调用 nextval() 也会增加计数器以避免锁定序列。
What I can't find addressed is the issue of what happens when you manually insert values into a field with a UNIQUE or PRIMARY constraint and a nextval() of a sequence as default. As far as I can see, this causes the INSERT to fail when the sequence reaches that value.
我找不到解决的是当您将值手动插入到具有 UNIQUE 或 PRIMARY 约束和序列的 nextval() 作为默认值的字段时会发生什么问题。据我所知，当序列达到该值时，这会导致 INSERT 失败。
Is there a simple (or common) way to fix this ?
有没有简单（或常见）的方法来解决这个问题？
A clear explanation would be very much appreciated.
一个明确的解释将不胜感激。
Update: If you feel I shouldn't do this, will never be able to fix this or am making some flawed assumptions, please feel free to point them out in your answers. Above all, please tell me what to do instead to offer programmers a stable and robust database that can't be corrupted with a simple insert (preferably without hiding everything behind stored procedures)
更新：如果你觉得我不应该这样做，永远无法解决这个问题或者我做出了一些有缺陷的假设，请随时在你的答案中指出它们。最重要的是，请告诉我该怎么做才能为程序员提供一个稳定且健壮的数据库，该数据库不会被简单的插入破坏（最好不要将所有内容隐藏在存储过程之后）
采纳答案by Nev Stokes
If you're migrating your data then I would drop the sequence constraint on the column, perform all of your inserts, use setval()to set the sequence to the maximum value of your data and then reinstate your column sequence nextval() default.
如果您要迁移数据，那么我将删除列上的序列约束，执行所有插入，使用setval()将序列设置为数据的最大值，然后恢复列序列 nextval() 默认值。
回答by Tometzky
You can create a trigger which will check if currval('id_sequence_name')>=NEW.id.
您可以创建一个触发器来检查是否currval('id_sequence_name')>=NEW.id.
If your transaction did not use default value or nextval('id_sequence_name'), then a currvalfunction will throw an error, as it works only when sequence was updated in current session. If you use nextvaland then try to insert bigger primary key then it will throw another error. A transaction will be then aborted.
如果您的事务没有使用默认值 or nextval('id_sequence_name')，则currval函数将抛出错误，因为它仅在当前会话中更新序列时才起作用。如果您使用nextval然后尝试插入更大的主键，则会引发另一个错误。然后将中止事务。
This would prevent inserting any bad primary keys which would break serial.
这将防止插入任何会破坏串行的坏主键。
Example code:
示例代码：
create table test (id serial primary key, value text);
create or replace function test_id_check() returns trigger language plpgsql as
$$ begin
if ( currval('test_id_seq')<NEW.id ) then
raise exception 'currval(test_id_seq)<id';
end if;
return NEW;
end; $$;
create trigger test_id_seq_check before insert or update of id on test
for each row execute procedure test_id_check();
Then inserting with default primary key will work fine:
然后使用默认主键插入将正常工作：
insert into test(value) values ('a'),('b'),('c'),('d');
But inserting too big primary key will error out and abort:
但是插入太大的主键会出错并中止：
insert into test(id, value) values (10,'z');
回答by Chris Pacejo
To expand on Tometzky's great answer, here is a more general version:
为了扩展 Tometzky 的精彩回答，这里有一个更通用的版本：
CREATE OR REPLACE FUNCTION check_serial() RETURNS trigger AS $$
BEGIN
IF currval(TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME || '_' || TG_ARGV[0] || '_seq') <
(row_to_json(NEW)->>TG_ARGV[0])::bigint
THEN RAISE SQLSTATE '55000'; -- same as currval() of uninitialized sequence
END IF;
RETURN NULL;
EXCEPTION
WHEN SQLSTATE '55000'
THEN RAISE 'manual entry of serial field %.%.% disallowed',
TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_ARGV[0]
USING HINT = 'use DEFAULT instead of specifying value manually',
SCHEMA = TG_TABLE_SCHEMA, TABLE = TG_TABLE_NAME, COLUMN = TG_ARGV[0];
END;
$$ LANGUAGE plpgsql;
Which you can apply to any column, say test.id, thusly:
您可以将其应用于任何列，例如 test.id，因此：
CREATE CONSTRAINT TRIGGER test_id_check
AFTER INSERT OR UPDATE OF id ON test
FOR EACH ROW EXECUTE PROCEDURE check_serial(id);
回答by knitti
I don't exactly understand you question, but if your goal is just to do the insert, and have a valid field (e.g. an id), then insert the values without the id field, that's what "default" stands for. It will work.
我不完全理解你的问题，但如果你的目标只是进行插入，并有一个有效的字段（例如一个 id），然后插入没有 id 字段的值，这就是“默认”代表的意思。它会起作用。
E.g. havin a id serial NOT NULLand a CONSTRAINT table_pkey PRIMARY KEY(id)in the table definition will auto-set the id and auto-increment a sequence table_id_seq.
例如，表定义中的aid serial NOT NULL和 aCONSTRAINT table_pkey PRIMARY KEY(id)将自动设置 id 并自动增加一个序列table_id_seq。
回答by Bruno Rafael Oliveira
What about using a CHECK?
使用 CHECK 怎么样？
CREATE SEQUENCE pk_test
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
CREATE TABLE test (
id INT PRIMARY KEY CHECK (id=currval('pk_test')) DEFAULT nextval('pk_test'),
num int not null
);
ALTER SEQUENCE pk_test OWNED BY test.id;
-- Testing:
INSERT INTO test (num) VALUES (3) RETURNING id, num;
1,3 -- OK
2,3 -- OK
INSERT INTO test (id, num) values (30,3) RETURNING id, num;
/*
ERROR: new row for relation "test" violates check constraint "test_id_check"
DETAIL: Failing row contains (30, 3).
********** Error **********
ERROR: new row for relation "test" violates check constraint "test_id_check"
SQL state: 23514
Detail: Failing row contains (30, 3).
*/
DROP TABLE test;
