blocks|key|243326|text|括号放错了位置：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|243327|SELECT+setval('payments_id_seq',+21,+true);++#+next+value+will+be+22|code-block|syntax|javascript|243328|否则，您将使用一个参数调用setval，而它需要两个或三个参数。|offset|length|style|CODE|243329|这与SELECT+setval('payments_id_seq',+21)相同|243330|entityMap^0|0|0|D|6|0|2|10|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@$I|T|J|U|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|V|8|@$I|W|J|X|K|L]]|9|@]|A|$]]|$1|O|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|P|$]]

The parentheses are misplaced:
<pre><code>SELECT setval('payments_id_seq', 21, true); # next value will be 22
</code></pre>
Otherwise you're calling <code>setval</code> with a single argument, while it requires two or three.
This is the same as <code>SELECT setval('payments_id_seq', 21)</code>

blocks|key|245804|text|使用select+setval('payments_id_seq',+21,+true);|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|245805|setval包含3个参数：|245806|下一个参数是sequence_name|245807|2nd参数，第一个参数是nextval|unordered-list-item|245808|
|245809|3rd参数，|245810|参数是可选的。|245811|245812|245813|setval第三个参数中true或false的用法如下：|245814|SELECT+setval('payments_id_seq',+21);+++++++++++//+Next+nextval+will+return+22
SELECT+setval('payments_id_seq',+21,+true);+++++//+Same+as+above+
SELECT+setval('payments_id_seq',+21,+false);++++//+Next+nextval+will+return+21|code-block|syntax|javascript|245815|为了避免sequence+name，next+sequence+value的硬编码，正确处理空列表，您可以使用以下方法：|245816|SELECT+setval(pg_get_serial_sequence('table_name',+'id'),+coalesce(max(id),+0)%2B1+,+false)+FROM+table_name;|245817|其中table_name是表的名称，id是表的primary+key|245818|entityMap^0|2|17|0|0|6|0|6|D|0|C|7|0|0|0|0|0|0|0|0|0|0|2|A|I|2|N|B|0^^$0|@$1|2|3|4|5|6|7|19|8|@$9|1A|A|1B|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|1C|8|@$9|1D|A|1E|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|1F|8|@$9|1G|A|1H|B|C]]|D|@]|E|$]]|$1|J|3|K|5|L|7|1I|8|@$9|1J|A|1K|B|C]]|D|@]|E|$]]|$1|M|3|N|5|6|7|1L|8|@]|D|@]|E|$]]|$1|O|3|P|5|L|7|1M|8|@]|D|@]|E|$]]|$1|Q|3|R|5|L|7|1N|8|@]|D|@]|E|$]]|$1|S|3|-4|5|6|7|1O|8|@]|D|@]|E|$]]|$1|T|3|-4|5|6|7|1P|8|@]|D|@]|E|$]]|$1|U|3|V|5|6|7|1Q|8|@]|D|@]|E|$]]|$1|W|3|X|5|Y|7|1R|8|@]|D|@]|E|$Z|10]]|$1|11|3|12|5|6|7|1S|8|@]|D|@]|E|$]]|$1|13|3|14|5|Y|7|1T|8|@]|D|@]|E|$Z|10]]|$1|15|3|16|5|6|7|1U|8|@$9|1V|A|1W|B|C]|$9|1X|A|1Y|B|C]|$9|1Z|A|20|B|C]]|D|@]|E|$]]|$1|17|3|-4|5|6|7|21|8|@]|D|@]|E|$]]]|18|$]]

Use <code>select setval('payments_id_seq', 21, true);</code>

<code>setval</code> contains 3 parameters:

<ul>
<li>1st parameter is <code>sequence_name</code></li>
<li>2nd parameter is Next <code>nextval</code></li>
<li>3rd parameter is optional.</li>
</ul>

The use of true or false in 3rd parameter of setval is as follows:

<pre><code>SELECT setval('payments_id_seq', 21); // Next nextval will return 22
SELECT setval('payments_id_seq', 21, true); // Same as above 
SELECT setval('payments_id_seq', 21, false); // Next nextval will return 21
</code></pre>

The better way to avoid hard-coding of sequence name, next sequence value and to handle empty column table correctly, you can use the below way:

<pre><code>SELECT setval(pg_get_serial_sequence('table_name', 'id'), coalesce(max(id), 0)+1 , false) FROM table_name;
</code></pre>

where <code>table_name</code> is the name of the table, <code>id</code> is the <code>primary key</code> of the table

blocks|key|244548|text|select设置name(‘sequence_name’，sequence_value)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|244549|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

select setval('sequence_name', sequence_value)

blocks|key|2915520|text|我不会尝试通过setval更改序列。但是在使用ALTER的时候，我被要求如何正确地写序列名。这只对我有效：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2915521|使用SELECT+*+FROM+information_schema.sequences;检查所需的|2915522|序列名称|ordered-list-item|2915523|2915524|2915525|ALTER+SEQUENCE+public."table_name_Id_seq"+restart+{number};|2915526|2915527|在我的例子中，它是ALTER+SEQUENCE+public."Services_Id_seq"+restart+8;|2915528|还有一个关于wiki.postgresql.org的页面，其中描述了一种生成sql脚本的方法，以一次修复所有数据库表中的序列。来自链接的文本下方：|2915529|2915530|将其保存到一个文件，如'reset.sql‘|blockquote|2915531|2915532|SELECT+'SELECT+SETVAL(‘%7C%7C+quote_literal(+quote_ident(PGT.schemaname)+%7C%7C’.%7C+quote_ident(S.relname))+%7C%7C+'，COALESCE(MAX(‘%7C%7Cquote_ident(C.attname)%7C%7C+')，1)+)+FROM’%7Cquote_ident(PGT.schemaname)+%7C%7C‘.+%7C%7C+quote_ident(T.relname)+%7C%7C+'；‘FROM+pg_class+AS+S，pg_depend+AS+D，pg_class+AS+T，pg_attribute+AS+C，pg_tables+AS+PGT，其中S.relkind+=+'S’和S.oid+=+D.objid和D.refobjid+=+T.oid和D.refobjid+=+C.attrelid和D.refobjsubid+=+C.attnum和T.relname+=+PGT.tablename按S.relname排序；|2915533|运行该文件并以不包含常用标头的方式保存其输出，然后运行该输出。示例：|2915534|psql+-Atq+-f+reset.sql+-o+temp+psql+-f+temp+rm+temp|2915535|2915536|输出将是一组sql命令，它们看起来完全像这样：|2915537|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";|code-block|syntax|javascript|2915538|entityMap|0|LINK|mutability|MUTABLE|url|https://wiki.postgresql.org/wiki/Fixing_Sequences^0|7|6|N|5|0|2|17|0|0|0|0|0|1N|0|0|9|1E|0|6|J|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1K|8|@$9|1L|A|1M|B|C]|$9|1N|A|1O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|1P|8|@$9|1Q|A|1R|B|C]]|D|@]|E|$]]|$1|H|3|I|5|J|7|1S|8|@]|D|@]|E|$]]|$1|K|3|-4|5|6|7|1T|8|@]|D|@]|E|$]]|$1|L|3|-4|5|6|7|1U|8|@]|D|@]|E|$]]|$1|M|3|N|5|J|7|1V|8|@$9|1W|A|1X|B|C]]|D|@]|E|$]]|$1|O|3|-4|5|6|7|1Y|8|@]|D|@]|E|$]]|$1|P|3|Q|5|6|7|1Z|8|@$9|20|A|21|B|C]]|D|@]|E|$]]|$1|R|3|S|5|6|7|22|8|@]|D|@$9|23|A|24|1|25]]|E|$]]|$1|T|3|-4|5|6|7|26|8|@]|D|@]|E|$]]|$1|U|3|V|5|W|7|27|8|@]|D|@]|E|$]]|$1|X|3|-4|5|6|7|28|8|@]|D|@]|E|$]]|$1|Y|3|Z|5|6|7|29|8|@]|D|@]|E|$]]|$1|10|3|11|5|6|7|2A|8|@]|D|@]|E|$]]|$1|12|3|13|5|6|7|2B|8|@]|D|@]|E|$]]|$1|14|3|-4|5|6|7|2C|8|@]|D|@]|E|$]]|$1|15|3|16|5|6|7|2D|8|@]|D|@]|E|$]]|$1|17|3|18|5|19|7|2E|8|@]|D|@]|E|$1A|1B]]|$1|1C|3|-4|5|6|7|2F|8|@]|D|@]|E|$]]]|1D|$1E|$5|1F|1G|1H|E|$1I|1J]]]]

I don't try changing sequence via <code>setval</code>. But using <code>ALTER</code> I was issued how to write sequence name properly. And this only work for me:
<ol>
<li>Check required sequence name using <code>SELECT * FROM information_schema.sequences;</code>
</li>
<li><code>ALTER SEQUENCE public.&quot;table_name_Id_seq&quot; restart {number};</code>
In my case it was <code>ALTER SEQUENCE public.&quot;Services_Id_seq&quot; restart 8;</code>
</li>
</ol>
Also there is a page on <a href="https://wiki.postgresql.org/wiki/Fixing_Sequences" rel="nofollow noreferrer">wiki.postgresql.org</a> where describes a way to generate sql script to fix sequences in all database tables at once. Below the text from link:
<blockquote>
Save this to a file, say 'reset.sql'
<pre><code>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;
</code></pre>
Run the file and save its output in a way that doesn't include the
usual headers, then run that output. Example:
<pre><code>psql -Atq -f reset.sql -o temp
psql -f temp
rm temp
</code></pre>
</blockquote>
And the output will be a set of sql commands which look exactly like this:
<pre><code>SELECT SETVAL('public.&quot;SocialMentionEvents_Id_seq&quot;', COALESCE(MAX(&quot;Id&quot;), 1) ) FROM public.&quot;SocialMentionEvents&quot;;
SELECT SETVAL('public.&quot;Users_Id_seq&quot;', COALESCE(MAX(&quot;Id&quot;), 1) ) FROM public.&quot;Users&quot;;
</code></pre>

blocks|key|2915540|text|这对我很有效：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2915541|SELECT+pg_catalog.setval('public.hibernate_sequence',+3,+true);|code-block|syntax|javascript|2915542|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

this worked for me:
<pre><code>SELECT pg_catalog.setval('public.hibernate_sequence', 3, true);
</code></pre>

I'm trying to set a sequence to a specific value.

<pre><code>SELECT setval('payments_id_seq'), 21, true
</code></pre>

This gives an error:

<blockquote>
 <code>ERROR: function setval(unknown) does not exist</code>
</blockquote>

Using <code>ALTER SEQUENCE</code> doesn't seem to work either?

<pre><code>ALTER SEQUENCE payments_id_seq LASTVALUE 22
</code></pre>

How can this be done?

Ref: <a href="https://www.postgresql.org/docs/current/static/functions-sequence.html" rel="noreferrer">https://www.postgresql.org/docs/current/static/functions-sequence.html</a>

Postgres manually alter sequence

我正在尝试将序列设置为特定值。SELECT setval('payments_id_seq'), 21, true这会给出一个错误： ERROR:  function setval(unknown) does not exist使用ALTER SEQUENCE似乎也不起作用？ALTER SEQUENCE payments_id_seq LASTVALUE 22如何做到这一点？参考：

问Postgres手动更改序列
EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Postgres手动更改序列EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Postgres手动更改序列
EN