Postgres manually alter sequence

Question

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/functions-sequence.html

Arithmomancy

It would appear that setval() has at least two arguments. – Excepting

Answer 1

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)

Fraze

What does the last argument "true" means? – Jerboa

true means that the next value will be the number provided + 1, in this case 22. false means that the next value would be the number provided, or 21. By default, setval will behave as if true was chosen. More details: postgresql.org/docs/9.6/static/functions-sequence.html – Runagate

an advantage of the select setval syntax over alter sequence is that you can use nested queries in it, for example to select max(id) from payments. – Dodder

@Dodder that's an excellent point, and one of the easiest ways to ensure you don't accidently introduce a latent bug by setting the sequence number lower than the current max – Wallacewallach

Another advantage of using such functions is running queries like select setval('new_id_seq', nextval('old_id_seq'), false); which helps to migrate – Concertgoer

In case when you need to continue sequence from the value of max id, you should use SELECT setval('payment_id_seq', (select coalesce(max(id), 0) from payment), true); – Acetylcholine

Answer 2

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.

Lankton

ALTER SEQUENCE [sequence] RESTART WITH (SELECT MAX(col) from table); does not work, whereas SELECT setval('sequence', (SELECT (MAX(col) from table), TRUE); does work. I get a syntax error. (Postgres 9.4) – Kowtko

No subquery allowed in a DDL command ("utility command"). See: https://mcmap.net/q/28780/-error-when-setting-n_distinct-using-a-plpgsql-variable – Lankton

@Kowtko that doesn't work either. Always gives syntax error function setval(unknown, numeric, boolean) does not exist. – Koball

@MitalPritmani: You may need type casts. Consider added instructions above. – Lankton

@Kowtko I think you mean SELECT setval('sequence', (SELECT MAX(col) from table), TRUE); otherwise your parens don't line up. – Postaxial

@dland: Aside: shorter & faster equivalent: SELECT setval('seq', max(col)) FROM tbl; See: https://mcmap.net/q/28911/-how-to-reset-postgres-39-primary-key-sequence-when-it-falls-out-of-sync – Lankton

I like the last one as it allows me to calculate the value :) – Markson

FWIW, 'ALTER SEQUENCE RESTART WITH' and setval are not 100% equivalent - in my testing, ALTER is blocking, and setval is not, which makes setval suitable for concurrent updates from different transactions - a feature I needed. – Lori

Answer 3

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

Deuced

Thank you! Last expression is exactly what I was looking for. It allows me to reserve the sequence values in order to insert by batch afterwards. – Upanishad

This is the most correct answer, the last SQL snippet is the most generic and effective. – Careerism

First comment 'Next nextval will return 22'. Just if the increment value of the sequence is 1. It will be 21 + increment value. Third comment 'Next nextval will return 21'. That's always true. Sometimes for generating less network traffic the app gets e.g. 10 ids to use without the need to ask 10 times. And next time the nextval return value will be increased by 10 instead of 1. See increment here postgresql.org/docs/current/sql-createsequence.html – Bobbi

Answer 4

select setval('sequence_name', sequence_value)

Anselme

Answer 5

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

Frontwards

Answer 6

this worked for me:

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

Homerus

Answer 7

This alter it works for me :

ALTER SEQUENCE payments_id_seq
    RESTART 17;

Galiot

Answer 8

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

Bathhouse