我试图将一个序列设置为一个特定的值。
SELECT setval('payments_id_seq'), 21, true;
这将给出一个错误:
错误:函数setval不存在
使用ALTER SEQUENCE似乎也不工作?
ALTER SEQUENCE payments_id_seq LASTVALUE 22;
如何做到这一点呢?
裁判:https://www.postgresql.org/docs/current/functions-sequence.html
我试图将一个序列设置为一个特定的值。
SELECT setval('payments_id_seq'), 21, true;
这将给出一个错误:
错误:函数setval不存在
使用ALTER SEQUENCE似乎也不工作?
ALTER SEQUENCE payments_id_seq LASTVALUE 22;
如何做到这一点呢?
裁判:https://www.postgresql.org/docs/current/functions-sequence.html
当前回答
我没有尝试通过setval改变序列。但是使用ALTER我被告知如何正确地写序列名。这只对我有用:
使用SELECT * FROM information_schema.sequences检查所需的序列名; 改变序列公共。"table_name_Id_seq" restart {number}; 在我的例子中,它是ALTER SEQUENCE public。"Services_Id_seq" restart 8;
此外,在wiki.postgresql.org上有一个页面,其中描述了一种生成sql脚本的方法,以一次性修复所有数据库表中的序列。以下文字来自链接:
Save this to a file, say 'reset.sql' SELECT 'SELECT SETVAL(' || quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname)) || ', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1) ) FROM ' || quote_ident(PGT.schemaname)|| '.'||quote_ident(T.relname)|| ';' FROM pg_class AS S, pg_depend AS D, pg_class AS T, pg_attribute AS C, pg_tables AS PGT WHERE S.relkind = 'S' AND S.oid = D.objid AND D.refobjid = T.oid AND D.refobjid = C.attrelid AND D.refobjsubid = C.attnum AND T.relname = PGT.tablename ORDER BY S.relname; Run the file and save its output in a way that doesn't include the usual headers, then run that output. Example: psql -Atq -f reset.sql -o temp psql -f temp rm temp
输出将是一组sql命令,看起来就像这样:
SELECT SETVAL('public."SocialMentionEvents_Id_seq"', COALESCE(MAX("Id"), 1) ) FROM public."SocialMentionEvents";
SELECT SETVAL('public."Users_Id_seq"', COALESCE(MAX("Id"), 1) ) FROM public."Users";
其他回答
这招对我很管用:
SELECT pg_catalog.setval('public.hibernate_sequence', 3, true);
选择setval('sequence_name', sequence_value)
括号放错了:
SELECT setval('payments_id_seq', 21, true); -- next value will be 22
否则,调用setval只需要一个参数,而它需要两个或三个参数。
这与SELECT setval('payments_id_seq', 21)相同
我没有尝试通过setval改变序列。但是使用ALTER我被告知如何正确地写序列名。这只对我有用:
使用SELECT * FROM information_schema.sequences检查所需的序列名; 改变序列公共。"table_name_Id_seq" restart {number}; 在我的例子中,它是ALTER SEQUENCE public。"Services_Id_seq" restart 8;
此外,在wiki.postgresql.org上有一个页面,其中描述了一种生成sql脚本的方法,以一次性修复所有数据库表中的序列。以下文字来自链接:
Save this to a file, say 'reset.sql' SELECT 'SELECT SETVAL(' || quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname)) || ', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1) ) FROM ' || quote_ident(PGT.schemaname)|| '.'||quote_ident(T.relname)|| ';' FROM pg_class AS S, pg_depend AS D, pg_class AS T, pg_attribute AS C, pg_tables AS PGT WHERE S.relkind = 'S' AND S.oid = D.objid AND D.refobjid = T.oid AND D.refobjid = C.attrelid AND D.refobjsubid = C.attnum AND T.relname = PGT.tablename ORDER BY S.relname; Run the file and save its output in a way that doesn't include the usual headers, then run that output. Example: psql -Atq -f reset.sql -o temp psql -f temp rm temp
输出将是一组sql命令,看起来就像这样:
SELECT SETVAL('public."SocialMentionEvents_Id_seq"', COALESCE(MAX("Id"), 1) ) FROM public."SocialMentionEvents";
SELECT SETVAL('public."Users_Id_seq"', COALESCE(MAX("Id"), 1) ) FROM public."Users";
此语法在任何版本的PostgreSQL中都无效:
ALTER SEQUENCE payments_id_seq LASTVALUE
这是可行的:
ALTER SEQUENCE payments_id_seq RESTART WITH 22;
和等价于:
SELECT setval('payments_id_seq', 22, FALSE);
更多关于ALTER SEQUENCE和SEQUENCE函数的内容请参见当前手册。
请注意,setval()期望(regclass, bigint)或(regclass, bigint, boolean)。在上面的例子中,我提供了无类型的字面量。这也有用。但是,如果将类型变量提供给函数,则可能需要显式类型转换来满足函数类型解析。如:
SELECT setval(my_text_variable::regclass, my_other_variable::bigint, FALSE);
对于重复操作,您可能会感兴趣:
ALTER SEQUENCE payments_id_seq START WITH 22; -- set default
ALTER SEQUENCE payments_id_seq RESTART; -- without value
START [WITH]存储一个默认的RESTART号码,用于后续无值的RESTART调用。最后一部分需要Postgres 8.4或更高版本。