Postgres manually alter sequence

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)

---

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.

---

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

---

select setval('sequence_name', sequence_value)

---

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";

---

this worked for me:

SELECT pg_catalog.setval('public.hibernate_sequence', 3, true);

---