Postgresql - 使用带有改变序列表达式的子查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2022559/
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
Postgresql - Using subqueries with alter sequence expressions
提问by Danmaxis
My question is kind of simple.
我的问题有点简单。
Is it possible to use subqueries within alter expressions in PostgreSQL?
是否可以在 PostgreSQL 的 alter 表达式中使用子查询？
I want to alter a sequence value based on a primary key column value.
我想根据主键列值更改序列值。
I tried using the following expression, but it wouldn't execute.
我尝试使用以下表达式，但它不会执行。
alter sequence public.sequenceX restart with (select max(table_id)+1 from table)
更改序列 public.sequenceX 重新启动（从表中选择 max(table_id)+1）
Thanks in advance
提前致谢
回答by Arthur Thomas
I don't believe you can do it like that but you should be able to use the setval function direction which is what the alter does.
我不相信你可以那样做，但你应该能够使用 setval 函数方向，这是alter所做的。
select setval('sequenceX', (select max(table_id)+1 from table), false)
The false will make it return the next sequence number as exactly what is given.
false 将使它返回下一个序列号，正如给定的那样。
回答by Duncan Smart
In addition if you have mixed case object names and you're getting an error like this:
此外，如果您有大小写混合的对象名称并且您收到如下错误：
ERROR: relation "public.mytable_id_seq" does not exist
... the following version using regclass should be useful:
...使用 regclass 的以下版本应该很有用：
select setval('"public"."MyTable_Id_seq"'::regclass, (select MAX("Id") FROM "public"."MyTable"))
