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 static functions-sequence html

User · Accepted Answer

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

User · Answer

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

User · Answer

setval  sequence name   sequence value

User · Answer

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

User · Answer

this worked for me  SELECT pg catalog setval  public hibernate sequence   3  true

User · Answer

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  quot table name Id seq quot  restart  number   In my case it was ALTER SEQUENCE public  quot Services Id seq quot  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  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

[postgresql] Postgres manually alter sequence

Examples related to postgresql

Examples related to database-sequence