Postgres manually alter sequence
PostgresqlDatabase SequencePostgresql Problem Overview
I'm trying to set a sequence to a specific value.
SELECT setval('payments_id_seq'), 21, true
This gives an error:
>ERROR: function setval(unknown) does not exist
Using ALTER SEQUENCE
doesn't seem to work either?
ALTER SEQUENCE payments_id_seq LASTVALUE 22
How can this be done?
Ref: https://www.postgresql.org/docs/current/static/functions-sequence.html
Postgresql Solutions
Solution 1 - Postgresql
The parentheses are misplaced:
SELECT setval('payments_id_seq', 21, true); # next value will be 22
Otherwise you're calling setval
with a single argument, while it requires two or three.
This is the same as SELECT setval('payments_id_seq', 21)
Solution 2 - Postgresql
This syntax isn't valid in any version of PostgreSQL:
ALTER SEQUENCE payments_id_seq LASTVALUE 22
This would work:
ALTER SEQUENCE payments_id_seq RESTART WITH 22;
And is equivalent to:
SELECT setval('payments_id_seq', 22, FALSE);
More in the current manual for ALTER SEQUENCE
and sequence functions.
Note that setval()
expects either (regclass, bigint)
or (regclass, bigint, boolean)
. In the above example I am providing untyped literals. That works too. But if you feed typed variables to the function you may need explicit type casts to satisfy function type resolution. Like:
SELECT setval(my_text_variable::regclass, my_other_variable::bigint, FALSE);
For repeated operations you might be interested in:
ALTER SEQUENCE payments_id_seq START WITH 22; -- set default
ALTER SEQUENCE payments_id_seq RESTART; -- without value
START [WITH]
stores a default RESTART
number, which is used for subsequent RESTART
calls without value. You need Postgres 8.4 or later for the last part.
Solution 3 - Postgresql
Use select setval('payments_id_seq', 21, true);
setval
contains 3 parameters:
- 1st parameter is
sequence_name
- 2nd parameter is Next
nextval
- 3rd parameter is optional.
The use of true or false in 3rd parameter of setval is as follows:
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
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:
SELECT setval(pg_get_serial_sequence('table_name', 'id'), coalesce(max(id), 0)+1 , false) FROM table_name;
where table_name
is the name of the table, id
is the primary key
of the table
Solution 4 - Postgresql
select setval('sequence_name', sequence_value)
Solution 5 - Postgresql
I don't try changing sequence via setval
. But using ALTER
I was issued how to write sequence name properly. And this only work for me:
-
Check required sequence name using
SELECT * FROM information_schema.sequences;
-
ALTER SEQUENCE public."table_name_Id_seq" restart {number};
In my case it was
ALTER SEQUENCE public."Services_Id_seq" restart 8;
Also there is a page on wiki.postgresql.org where describes a way to generate sql script to fix sequences in all database tables at once. Below the text from link:
> 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
And the output will be a set of sql commands which look exactly like this:
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";
Solution 6 - Postgresql
this worked for me:
SELECT pg_catalog.setval('public.hibernate_sequence', 3, true);