Postgres manually alter sequence

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

To restart the sequence of a serial or IDENTITY column after the currently greatest column value (without even knowing the sequence name):

SELECT setval(pg_get_serial_sequence('payments', 'id') , COALESCE(max(id) + 1, 1) , false) FROM payments; 

See:

• How to reset Postgres' primary key sequence when it falls out of sync?
• Auto increment table column

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:

1. Check required sequence name using SELECT * FROM information_schema.sequences;

2. 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); 

This alter it works for me :

ALTER SEQUENCE payments_id_seq RESTART 17; 

For most of the people setval command should work, mine was not working so I used pg_catalog.setval.

This one worked for me:

select pg_catalog.setval('<sequence_variable_name>', <sequence_number_you_want>, true); 

The setval function alone didn't work for me.

SELECT pg_catalog.setval('payments_id_seq', 21, true); -- next will be 22 

See the docs for more info

To change sequence you can try:

ALTER SEQUENCE 'name_of_sequence' RESTART WITH 20; 

Now the sequence will start from 20.

If you don't know the sequence name you can get it like that:

SELECT pg_get_serial_sequence('table_name', 'id');