How to reset postgres primary key sequence when it falls out of sync

Question

I ran into the problem that my primary key sequence is not in sync with my table rows    That is  when I insert a new row I get a duplicate key error because the sequence implied in the serial datatype returns a number that already exists   It seems to be caused by import restores not maintaining the sequence properly

User · Answer

To restart all sequence to 1 use   -- Create Function CREATE OR REPLACE FUNCTION  sy restart seq to 1        relname TEXT   RETURNS  pg catalog   void  AS  BODY   DECLARE  BEGIN     EXECUTE  ALTER SEQUENCE    relname    RESTART WITH 1    END   BODY   LANGUAGE  plpgsql    -- Use Function SELECT      relname      sy restart seq to 1 relname  FROM pg class WHERE relkind    S

User · Answer

Try reindex   UPDATE  As pointed out in the comments  this was in reply to the original question

User · Answer

There are a lot of good answers here   I had the same need after reloading my Django database   But I needed    All in one Function Could fix one or more schemas at a time Could fix all or just one table at a time Also wanted a nice way to see exactly what had changed  or not changed   This seems very similar need to what the original ask was for  Thanks to Baldiry and Mauro got me on the right track   drop function IF EXISTS reset sequences text    text  RESTRICT  CREATE OR REPLACE FUNCTION reset sequences      in schema name list text        django    dbaas    metrics    monitor    runner    db counts         in table name text        RETURNS text   as  body    DECLARE changed seqs text      DECLARE sequence defs RECORD  c integer     BEGIN     FOR sequence defs IN         select           DISTINCT ccu table name  as table name            ccu column name as column name            replace replace c column default      regclass        nextval         as sequence name           from information schema constraint column usage ccu                 information schema columns c           where ccu table schema   ANY in schema name list              and ccu table schema   c table schema             AND c table name   ccu table name             and c table name like in table name             AND ccu column name   c column name             AND c column default is not null           ORDER BY sequence name    LOOP       EXECUTE  select max      sequence defs column name       from      sequence defs table name INTO c        IF c is null THEN c   1  else c   c   1  END IF        EXECUTE  alter sequence      sequence defs sequence name      restart  with      c        changed seqs   array append changed seqs   alter sequence      sequence defs sequence name      restart with      c      END LOOP     changed seqs   array append changed seqs   Done        RETURN changed seqs  END  body  LANGUAGE plpgsql    Then to Execute and See the changes run   select   from unnest reset sequences    django    dbaas    metrics    monitor    runner    db counts         Returns  activity id seq                          restart at 22 api connection info id seq               restart at 4 api user id seq                          restart at 1 application contact id seq               restart at 20

User · Answer

Recheck all sequence in public schema function  CREATE OR REPLACE FUNCTION public recheck sequence     RETURNS void AS  body  DECLARE    table name VARCHAR     column name VARCHAR       sequence name VARCHAR  BEGIN   FOR  table name IN SELECT tablename FROM pg catalog pg tables WHERE schemaname    public  LOOP     FOR  column name IN SELECT column name FROM information schema columns WHERE table name    table name LOOP         SELECT pg get serial sequence  table name   column name  INTO  sequence name          IF  sequence name IS NOT NULL THEN              EXECUTE  SELECT setval       sequence name       COALESCE  SELECT MAX    quote ident  column name      1 FROM    quote ident  table name       1   FALSE             END IF      END LOOP       END LOOP  END   body  LANGUAGE  plpgsql  VOLATILE CALLED ON NULL INPUT SECURITY INVOKER COST 100

User · Answer

This will reset all sequences from public making no assumptions about table or column names  Tested on version 8 4 CREATE OR REPLACE FUNCTION  quot reset sequence quot   tablename text  columnname text  sequence name text       RETURNS  quot pg catalog quot   quot void quot  AS            body          DECLARE        BEGIN             EXECUTE  SELECT setval         sequence name                 SELECT MAX      columnname                  FROM      tablename             1               END              body   LANGUAGE  plpgsql             SELECT table name           column name      seq        reset sequence table name  column name  table name           column name      seq    FROM information schema columns where column default like  nextval

User · Answer

The Klaus answer is the most useful  execpt for a little miss   you have to add DISTINCT in select statement   However  if you are sure that no table column names can be equivalent for two different tables  you can also use    select sequence name  --PG CLASS relname  PG ATTRIBUTE attname        reset sequence split part sequence name    id seq  1   from PG CLASS join PG ATTRIBUTE on PG ATTRIBUTE attrelid   PG CLASS oid join information schema sequences      on information schema sequences sequence name   PG CLASS relname           PG ATTRIBUTE attname where sequence schema  public     which is an extension of user457226 solution for the case when some interested column name is not  ID

User · Answer

I suggest this solution found on postgres wiki  It updates all sequences of your tables   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    How to use from postgres wiki     Save this to a file  say  reset sql  Run the file and save its output in a way that doesn t include the usual headers  then run that output  Example    Example   psql -Atq -f reset sql -o temp psql -f temp rm temp   Original article also with fix for sequence ownership  here

User · Answer

This issue happens with me when using entity framework to create the database and then seed the database with initial data  this makes the sequence mismatch   I Solved it by Creating a script to run after seeding the database   DO  do  DECLARE tablename text  BEGIN     -- change the where statments to include or exclude whatever tables you need     FOR tablename IN SELECT table name FROM information schema tables WHERE table schema  public  AND table type  BASE TABLE  AND table name       EFMigrationsHistory          LOOP             EXECUTE format  SELECT setval pg get serial sequence     s       Id      SELECT MAX  Id     1 from   s      tablename  tablename       END LOOP  END  do

User · Answer

These functions are fraught with perils when sequence names  column names  table names or schema names have funny characters such as spaces  punctuation marks  and the like   I have written this   CREATE OR REPLACE FUNCTION sequence max value oid  RETURNS bigint VOLATILE STRICT LANGUAGE plpgsql AS     DECLARE  tabrelid oid   colname name   r record   newmax bigint  BEGIN  FOR tabrelid  colname IN SELECT attrelid  attname                FROM pg attribute               WHERE  attrelid  attnum  IN                         SELECT adrelid  regclass adnum                         FROM pg attrdef                        WHERE oid IN  SELECT objid                                        FROM pg depend                                       WHERE refobjid    1                                             AND classid    pg attrdef   regclass                                                   LOOP       FOR r IN EXECUTE  SELECT max      quote ident colname        FROM      tabrelid  regclass LOOP           IF newmax IS NULL OR r max  gt  newmax THEN               newmax    r max            END IF        END LOOP    END LOOP    RETURN newmax  END         You can call it for a single sequence by passing it the OID and it will return the highest number used by any table that has the sequence as default  or you can run it with a query like this  to reset all the sequences in your database    select relname  setval oid  sequence max value oid      from pg class   where relkind    S     Using a different qual you can reset only the sequence in a certain schema  and so on   For example  if you want to adjust sequences in the  public  schema   select relname  setval pg class oid  sequence max value pg class oid     from pg class  pg namespace  where pg class relnamespace   pg namespace oid and        nspname    public  and        relkind    S     Note that due to how setval   works  you don t need to add 1 to the result   As a closing note  I have to warn that some databases seem to have defaults linking to sequences in ways that do not let the system catalogs have full information of them   This happens when you see things like this in psql s  d   alvherre    d baz                      Tabla   public baz    Columna    Tipo                     Modificadores                   --------- --------- ------------------------------------------------  a         integer   default nextval   foo a seq   text   regclass    Note that the nextval   call in that default clause has a   text cast in addition to the   regclass cast   I think this is due to databases being pg dump ed from old PostgreSQL versions   What will happen is that the function sequence max value   above will ignore such a table   To fix the problem  you can redefine the DEFAULT clause to refer to the sequence directly without the cast   alvherre   alter table baz alter a set default nextval  foo a seq    ALTER TABLE   Then psql displays it properly   alvherre    d baz                      Tabla   public baz    Columna    Tipo                 Modificadores               --------- --------- ----------------------------------------  a         integer   default nextval  foo a seq   regclass    As soon as you ve fixed that  the function works correctly for this table as well as all others that might use the same sequence

User · Answer

The Klaus answer is the most useful  execpt for a little miss   you have to add DISTINCT in select statement   However  if you are sure that no table column names can be equivalent for two different tables  you can also use    select sequence name  --PG CLASS relname  PG ATTRIBUTE attname        reset sequence split part sequence name    id seq  1   from PG CLASS join PG ATTRIBUTE on PG ATTRIBUTE attrelid   PG CLASS oid join information schema sequences      on information schema sequences sequence name   PG CLASS relname           PG ATTRIBUTE attname where sequence schema  public     which is an extension of user457226 solution for the case when some interested column name is not  ID

User · Answer

The shortest and fastest way  SELECT setval  tbl tbl id seq   max tbl id   FROM tbl   tbl id being the serial column of table tbl  drawing from the sequence tbl tbl id seq  which is the default automatic name   If you don t know the name of the attached sequence  which doesn t have to be in default form   use pg get serial sequence    SELECT setval pg get serial sequence  tbl    tbl id    max tbl id   FROM tbl   There is no off-by-one error here  The manual   The two-parameter form sets the sequence s last value field to the specified value and sets its is called field to true  meaning that the next nextval will advance the sequence before returning a value   Bold emphasis mine  If the table can be empty and to actually start from 1 in this case  SELECT setval pg get serial sequence  tbl    tbl id                 COALESCE max tbl id    1  1                false  FROM tbl   We can t just use the 2-parameter form and start with 0 because the lower bound of sequences is 1 by default  unless customized   Concurrency To defend against concurrent sequence activity or writes to the table in the above queries  lock the table in SHARE mode  It keeps concurrent transactions from writing a higher number  or anything at all   To also take clients into account that may have fetched sequence numbers in advance without any locks on the main table  yet  can happen in certain setups   only increase the current value of the sequence  never decrease it  It may seem paranoid  but that s in accord with the nature of sequences and defending against concurrency issues  BEGIN   LOCK TABLE tbl IN SHARE MODE   SELECT setval  tbl tbl id seq   max tbl id   FROM   tbl HAVING max tbl id   gt   SELECT last value FROM tbl tbl id seq   -- prevent lower number  COMMIT   SHARE mode is strong enough for the purpose  The manual   This mode protects a table against concurrent data changes   It conflicts with ROW EXCLUSIVE mode   The commands UPDATE  DELETE  and INSERT acquire this lock mode on the target table

User · Answer

pg get serial sequence can be used to avoid any incorrect assumptions about the sequence name  This resets the sequence in one shot   SELECT pg catalog setval pg get serial sequence  table name    id     SELECT MAX id  FROM table name  1     Or more concisely   SELECT pg catalog setval pg get serial sequence  table name    id    MAX id   FROM table name    However this form can t handle empty tables correctly  since max id  is null  and neither can you setval 0 because it would be out of range of the sequence  One workaround for this is to resort to the ALTER SEQUENCE syntax i e   ALTER SEQUENCE table name id seq RESTART WITH 1  ALTER SEQUENCE table name id seq RESTART  -- 8 4 or higher   But ALTER SEQUENCE is of limited use because the sequence name and restart value cannot be expressions   It seems the best all-purpose solution is to call setval with false as the 3rd parameter  allowing us to specify the  next value to use    SELECT setval pg get serial sequence  t1    id    coalesce max id  0    1  false  FROM t1    This ticks all my boxes    avoids hard-coding the actual sequence name  handles empty tables correctly  handles tables with existing data  and does not leave a hole in the sequence   Finally  note that pg get serial sequence only works if the sequence is owned by the column  This will be the case if the incrementing column was defined as a serial type  however if the sequence was added manually it is necessary to ensure ALTER SEQUENCE    OWNED BY is also performed    i e  if serial type was used for table creation  this should all work   CREATE TABLE t1     id serial    name varchar 20      SELECT pg get serial sequence  t1    id    -- returns  t1 id seq   -- reset the sequence  regardless whether table has rows or not  SELECT setval pg get serial sequence  t1    id    coalesce max id  0    1  false  FROM t1    But if sequences were added manually   CREATE TABLE t2     id integer NOT NULL    name varchar 20      CREATE SEQUENCE t2 custom id seq     START WITH 1     INCREMENT BY 1     NO MINVALUE     NO MAXVALUE     CACHE 1   ALTER TABLE t2 ALTER COLUMN id SET DEFAULT nextval  t2 custom id seq   regclass    ALTER SEQUENCE t2 custom id seq OWNED BY t2 id  -- required for pg get serial sequence  SELECT pg get serial sequence  t2    id    -- returns  t2 custom id seq   -- reset the sequence  regardless whether table has rows or not  SELECT setval pg get serial sequence  t2    id    coalesce max id  0    1  false  FROM t1

User · Answer

ALTER SEQUENCE sequence name RESTART WITH  SELECT max id  FROM table name   Doesn t work   Copied from  tardate answer   SELECT setval pg get serial sequence  table name    id    MAX id   FROM table name

User · Answer

before I had not tried yet the code   in the following I post the version for the sql-code for both Klaus and user457226 solutions which worked on my pc  Postgres 8 3   with just some little adjustements for the Klaus one and of my version for the user457226 one   Klaus solution    drop function IF EXISTS rebuilt sequences   RESTRICT  CREATE OR REPLACE FUNCTION  rebuilt sequences   RETURNS integer as  body    DECLARE sequencedefs RECORD  c integer     BEGIN     FOR sequencedefs IN Select       constraint column usage table name as tablename        constraint column usage table name as tablename         constraint column usage column name as columnname        replace replace columns column default      regclass        nextval         as sequencename       from information schema constraint column usage  information schema columns       where constraint column usage table schema   public  AND        columns table schema    public  AND columns table name constraint column usage table name       AND constraint column usage column name   columns column name       AND columns column default is not null    LOOP           EXECUTE  select max    sequencedefs columnname     from      sequencedefs tablename INTO c        IF c is null THEN c   0  END IF        IF c is not null THEN c   c  1  END IF        EXECUTE  alter sequence      sequencedefs sequencename     restart  with      c     END LOOP      RETURN 1  END   body  LANGUAGE plpgsql   select rebuilt sequences      user457226 solution    --drop function IF EXISTS reset sequence  text text  RESTRICT  CREATE OR REPLACE FUNCTION  reset sequence   tablename text columnname text  RETURNS bigint -- pg catalog   void  AS  body    DECLARE seqname character varying            c integer    BEGIN     select tablename           columnname      seq  into seqname      EXECUTE  SELECT max       columnname        FROM       tablename        into c      if c is null then c   0  end if      c   c 1  --because of substitution of setval with  alter sequence      --EXECUTE  SELECT setval        seqname             cast c as character varying        false    DOES NOT WORK        EXECUTE  alter sequence      seqname     restart with      cast c as character varying       RETURN nextval seqname -1    END   body  LANGUAGE  plpgsql    select sequence name  PG CLASS relname  PG ATTRIBUTE attname         reset sequence PG CLASS relname PG ATTRIBUTE attname  from PG CLASS join PG ATTRIBUTE on PG ATTRIBUTE attrelid   PG CLASS oid join information schema sequences      on information schema sequences sequence name   PG CLASS relname           PG ATTRIBUTE attname      seq  where sequence schema  public

User · Answer

ALTER SEQUENCE sequence name RESTART WITH  SELECT max id  FROM table name   Doesn t work   Copied from  tardate answer   SELECT setval pg get serial sequence  table name    id    MAX id   FROM table name

User · Answer

Reset all sequence from public  CREATE OR REPLACE FUNCTION  reset sequence   tablename text  RETURNS  pg catalog   void  AS   body      DECLARE    BEGIN    EXECUTE  SELECT setval            tablename          id seq              SELECT id   1 FROM          tablename           ORDER BY id DESC LIMIT 1   false        END     body   LANGUAGE  plpgsql    select sequence name  reset sequence split part sequence name    id seq  1   from information schema sequences         where sequence schema  public

User · Answer

-- Login to psql and run the following  -- What is the result  SELECT MAX id  FROM your table   -- Then run    -- This should be higher than the last result  SELECT nextval  your table id seq     -- If it s not higher    run this set the sequence last to your highest id   --  wise to run a quick pg dump first      BEGIN  -- protect against concurrent inserts while you update the counter LOCK TABLE your table IN EXCLUSIVE MODE  -- Update the sequence SELECT setval  your table id seq   COALESCE  SELECT MAX id  1 FROM your table   1   false   COMMIT    Source - Ruby Forum

User · Answer

Try reindex   UPDATE  As pointed out in the comments  this was in reply to the original question

User · Answer

select  SELECT SETVAL      seq   1        COALESCE MAX    column name     1  1    FROM    table name      from          SELECT table name  column name  column default  regexp match column default            as seq        from information schema columns        where column default ilike  nextval          as sequense query

User · Answer

This command for only change auto generated key sequence value in postgresql   ALTER SEQUENCE  your sequence name  RESTART WITH 0    In place of zero you can put any number from which you want to restart sequence   default sequence name will  TableName FieldName seq   For example  if your table name is  MyTable  and your field name is  MyID   then your sequence name will be  MyTable MyID seq    This is answer is same as  murugesanponappan s answer  but there is a syntax error in his solution  you can not use sub query  select max       in alter command  So that either you have to use fixed numeric value or you need to use a variable in place of sub query

User · Answer

A method to update all sequences in your schema that are used as an ID   DO    DECLARE   r RECORD  BEGIN FOR r IN  SELECT tablename  pg get serial sequence tablename   id   as sequencename           FROM pg catalog pg tables           WHERE schemaname  YOUR SCHEMA            AND tablename IN  SELECT table name                              FROM information schema columns                              WHERE table name tablename and column name  id             order by tablename  LOOP EXECUTE          SELECT setval        r sequencename         COALESCE MAX id   1   MAX id  IS NOT null           FROM      r tablename         END LOOP  END

User · Answer

This command for only change auto generated key sequence value in postgresql   ALTER SEQUENCE  your sequence name  RESTART WITH 0    In place of zero you can put any number from which you want to restart sequence   default sequence name will  TableName FieldName seq   For example  if your table name is  MyTable  and your field name is  MyID   then your sequence name will be  MyTable MyID seq    This is answer is same as  murugesanponappan s answer  but there is a syntax error in his solution  you can not use sub query  select max       in alter command  So that either you have to use fixed numeric value or you need to use a variable in place of sub query

User · Answer

My version use the first one  with some error checking     BEGIN  CREATE OR REPLACE FUNCTION reset sequence  table schema text   tablename text   columnname text   sequence name text  RETURNS pg catalog void AS  BODY  DECLARE BEGIN  PERFORM 1  FROM information schema sequences  WHERE   sequence schema    table schema AND   sequence name    sequence name   IF FOUND THEN   EXECUTE  SELECT setval          table schema            sequence name                 SELECT MAX       columnname       FROM       table schema            tablename             1     ELSE   RAISE WARNING  SEQUENCE NOT UPDATED ON        tablename   columnname   END IF  END    BODY   LANGUAGE  plpgsql    SELECT reset sequence table schema  table name  column name  table name           column name      seq   FROM information schema columns WHERE column default LIKE  nextval     DROP FUNCTION reset sequence  table schema text   tablename text   columnname text   sequence name text    COMMIT

User · Answer

There are a lot of good answers here   I had the same need after reloading my Django database   But I needed    All in one Function Could fix one or more schemas at a time Could fix all or just one table at a time Also wanted a nice way to see exactly what had changed  or not changed   This seems very similar need to what the original ask was for  Thanks to Baldiry and Mauro got me on the right track   drop function IF EXISTS reset sequences text    text  RESTRICT  CREATE OR REPLACE FUNCTION reset sequences      in schema name list text        django    dbaas    metrics    monitor    runner    db counts         in table name text        RETURNS text   as  body    DECLARE changed seqs text      DECLARE sequence defs RECORD  c integer     BEGIN     FOR sequence defs IN         select           DISTINCT ccu table name  as table name            ccu column name as column name            replace replace c column default      regclass        nextval         as sequence name           from information schema constraint column usage ccu                 information schema columns c           where ccu table schema   ANY in schema name list              and ccu table schema   c table schema             AND c table name   ccu table name             and c table name like in table name             AND ccu column name   c column name             AND c column default is not null           ORDER BY sequence name    LOOP       EXECUTE  select max      sequence defs column name       from      sequence defs table name INTO c        IF c is null THEN c   1  else c   c   1  END IF        EXECUTE  alter sequence      sequence defs sequence name      restart  with      c        changed seqs   array append changed seqs   alter sequence      sequence defs sequence name      restart with      c      END LOOP     changed seqs   array append changed seqs   Done        RETURN changed seqs  END  body  LANGUAGE plpgsql    Then to Execute and See the changes run   select   from unnest reset sequences    django    dbaas    metrics    monitor    runner    db counts         Returns  activity id seq                          restart at 22 api connection info id seq               restart at 4 api user id seq                          restart at 1 application contact id seq               restart at 20

User · Answer

These functions are fraught with perils when sequence names  column names  table names or schema names have funny characters such as spaces  punctuation marks  and the like   I have written this   CREATE OR REPLACE FUNCTION sequence max value oid  RETURNS bigint VOLATILE STRICT LANGUAGE plpgsql AS     DECLARE  tabrelid oid   colname name   r record   newmax bigint  BEGIN  FOR tabrelid  colname IN SELECT attrelid  attname                FROM pg attribute               WHERE  attrelid  attnum  IN                         SELECT adrelid  regclass adnum                         FROM pg attrdef                        WHERE oid IN  SELECT objid                                        FROM pg depend                                       WHERE refobjid    1                                             AND classid    pg attrdef   regclass                                                   LOOP       FOR r IN EXECUTE  SELECT max      quote ident colname        FROM      tabrelid  regclass LOOP           IF newmax IS NULL OR r max  gt  newmax THEN               newmax    r max            END IF        END LOOP    END LOOP    RETURN newmax  END         You can call it for a single sequence by passing it the OID and it will return the highest number used by any table that has the sequence as default  or you can run it with a query like this  to reset all the sequences in your database    select relname  setval oid  sequence max value oid      from pg class   where relkind    S     Using a different qual you can reset only the sequence in a certain schema  and so on   For example  if you want to adjust sequences in the  public  schema   select relname  setval pg class oid  sequence max value pg class oid     from pg class  pg namespace  where pg class relnamespace   pg namespace oid and        nspname    public  and        relkind    S     Note that due to how setval   works  you don t need to add 1 to the result   As a closing note  I have to warn that some databases seem to have defaults linking to sequences in ways that do not let the system catalogs have full information of them   This happens when you see things like this in psql s  d   alvherre    d baz                      Tabla   public baz    Columna    Tipo                     Modificadores                   --------- --------- ------------------------------------------------  a         integer   default nextval   foo a seq   text   regclass    Note that the nextval   call in that default clause has a   text cast in addition to the   regclass cast   I think this is due to databases being pg dump ed from old PostgreSQL versions   What will happen is that the function sequence max value   above will ignore such a table   To fix the problem  you can redefine the DEFAULT clause to refer to the sequence directly without the cast   alvherre   alter table baz alter a set default nextval  foo a seq    ALTER TABLE   Then psql displays it properly   alvherre    d baz                      Tabla   public baz    Columna    Tipo                 Modificadores               --------- --------- ----------------------------------------  a         integer   default nextval  foo a seq   regclass    As soon as you ve fixed that  the function works correctly for this table as well as all others that might use the same sequence

User · Answer

If you see this error when you are loading custom SQL data for initialization  another way to avoid this is   Instead of writing   INSERT INTO book  id  name  price  VALUES  1    Alchemist    10     Remove the id  primary key  from initial data  INSERT INTO book  name  price  VALUES   Alchemist    10     This keeps the Postgres sequence in sync

User · Answer

Ugly hack to fix it using some shell magic  not a great solution but might inspire others with similar problems     pg dump -s  lt DATABASE gt    grep  CREATE TABLE    awk   print  SELECT setval     3   id seq    SELECT MAX id  FROM    3           sed  s     g    psql  lt DATABASE gt  -f -

User · Answer

I spent an hour trying to get djsnowsill s answer to work with a database using Mixed Case tables and columns  then finally stumbled upon the solution thanks to a comment from Manuel Darveau  but I thought I could make it a bit clearer for everyone   CREATE OR REPLACE FUNCTION  reset sequence   tablename text  columnname text  RETURNS  pg catalog   void  AS  body  DECLARE BEGIN EXECUTE format  SELECT setval pg get serial sequence    1 I     2 L            SELECT COALESCE MAX  2 I  1 1  FROM  1 I   false   tablename columnname   END   body   LANGUAGE  plpgsql    SELECT format   s  s seq  table name column name   reset sequence table name column name   FROM information schema columns WHERE column default like  nextval      This has the benefit of    not assuming ID column is spelled a particular way  not assuming all tables have a sequence  working for Mixed Case table column names   using format to be more concise    To explain  the problem was that pg get serial sequence takes strings to work out what you re referring to  so if you do    TableName  --it thinks it s a table or column  TableName  --it thinks it s a string  but makes it lower case   TableName   --it works    This is achieved using    1 I   in the format string     makes an apostrophe 1  means first arg  and I means in quotes

User · Answer

ALTER SEQUENCE sequence name RESTART WITH  SELECT max id  FROM table name   Doesn t work   Copied from  tardate answer   SELECT setval pg get serial sequence  table name    id    MAX id   FROM table name

User · Answer

-- Login to psql and run the following  -- What is the result  SELECT MAX id  FROM your table   -- Then run    -- This should be higher than the last result  SELECT nextval  your table id seq     -- If it s not higher    run this set the sequence last to your highest id   --  wise to run a quick pg dump first      BEGIN  -- protect against concurrent inserts while you update the counter LOCK TABLE your table IN EXCLUSIVE MODE  -- Update the sequence SELECT setval  your table id seq   COALESCE  SELECT MAX id  1 FROM your table   1   false   COMMIT    Source - Ruby Forum

User · Answer

Putting it all together  CREATE OR REPLACE FUNCTION  reset sequence   tablename text   RETURNS  pg catalog   void  AS  body  DECLARE BEGIN   EXECUTE  SELECT setval  pg get serial sequence        tablename           id        SELECT COALESCE MAX id  1 1  FROM      tablename        false    END   body   LANGUAGE  plpgsql     will fix  id  sequence of the given table  as usually necessary with django for instance

User · Answer

A method to update all sequences in your schema that are used as an ID   DO    DECLARE   r RECORD  BEGIN FOR r IN  SELECT tablename  pg get serial sequence tablename   id   as sequencename           FROM pg catalog pg tables           WHERE schemaname  YOUR SCHEMA            AND tablename IN  SELECT table name                              FROM information schema columns                              WHERE table name tablename and column name  id             order by tablename  LOOP EXECUTE          SELECT setval        r sequencename         COALESCE MAX id   1   MAX id  IS NOT null           FROM      r tablename         END LOOP  END

User · Answer

Try reindex   UPDATE  As pointed out in the comments  this was in reply to the original question

User · Answer

Just run below command   SELECT setval  my table seq    SELECT max id  FROM my table

User · Answer

Reset all sequences  no assumptions about names except that the primary key of each table is  id    CREATE OR REPLACE FUNCTION  reset sequence   tablename text  columnname text  RETURNS  pg catalog   void  AS  body  DECLARE BEGIN     EXECUTE  SELECT setval  pg get serial sequence        tablename                columnname               SELECT COALESCE MAX id  1 1  FROM      tablename        false    END   body   LANGUAGE  plpgsql    select table name           column name      seq   reset sequence table name  column name  from information schema columns where column default like  nextval

User · Answer

select  SELECT SETVAL      seq   1        COALESCE MAX    column name     1  1    FROM    table name      from          SELECT table name  column name  column default  regexp match column default            as seq        from information schema columns        where column default ilike  nextval          as sequense query

User · Answer

Just run below command   SELECT setval  my table seq    SELECT max id  FROM my table

User · Answer

To restart all sequence to 1 use   -- Create Function CREATE OR REPLACE FUNCTION  sy restart seq to 1        relname TEXT   RETURNS  pg catalog   void  AS  BODY   DECLARE  BEGIN     EXECUTE  ALTER SEQUENCE    relname    RESTART WITH 1    END   BODY   LANGUAGE  plpgsql    -- Use Function SELECT      relname      sy restart seq to 1 relname  FROM pg class WHERE relkind    S

User · Answer

Yet another plpgsql - resets only if max att   gt  then lastval  do --check seq not in sync    declare   r record    i bigint    m bigint  begin   for  r in       SELECT relname nspname d refobjid  regclass  a attname  refobjid     FROM   pg depend    d     JOIN   pg attribute a ON a attrelid   d refobjid AND a attnum   d refobjsubid     JOIN pg class r on r oid   objid     JOIN pg namespace n on n oid   relnamespace     WHERE  d refobjsubid  gt  0 and  relkind    S       loop     execute format  select last value from  I  I   r nspname  r relname  into  i      execute format  select max  I  from  s   r attname  r refobjid  into  m      if coalesce  m 0   gt   i then       raise info     concat  changed     r nspname      r relname   from    i   to    m         execute format  alter sequence  I  I restart with  s   r nspname  r relname  m 1       end if    end loop   end         also commenting the line --execute format  alter sequence will give the list  not actually resetting the value

User · Answer

Putting it all together  CREATE OR REPLACE FUNCTION  reset sequence   tablename text   RETURNS  pg catalog   void  AS  body  DECLARE BEGIN   EXECUTE  SELECT setval  pg get serial sequence        tablename           id        SELECT COALESCE MAX id  1 1  FROM      tablename        false    END   body   LANGUAGE  plpgsql     will fix  id  sequence of the given table  as usually necessary with django for instance

User · Answer

Yet another plpgsql - resets only if max att   gt  then lastval  do --check seq not in sync    declare   r record    i bigint    m bigint  begin   for  r in       SELECT relname nspname d refobjid  regclass  a attname  refobjid     FROM   pg depend    d     JOIN   pg attribute a ON a attrelid   d refobjid AND a attnum   d refobjsubid     JOIN pg class r on r oid   objid     JOIN pg namespace n on n oid   relnamespace     WHERE  d refobjsubid  gt  0 and  relkind    S       loop     execute format  select last value from  I  I   r nspname  r relname  into  i      execute format  select max  I  from  s   r attname  r refobjid  into  m      if coalesce  m 0   gt   i then       raise info     concat  changed     r nspname      r relname   from    i   to    m         execute format  alter sequence  I  I restart with  s   r nspname  r relname  m 1       end if    end loop   end         also commenting the line --execute format  alter sequence will give the list  not actually resetting the value

User · Answer

before I had not tried yet the code   in the following I post the version for the sql-code for both Klaus and user457226 solutions which worked on my pc  Postgres 8 3   with just some little adjustements for the Klaus one and of my version for the user457226 one   Klaus solution    drop function IF EXISTS rebuilt sequences   RESTRICT  CREATE OR REPLACE FUNCTION  rebuilt sequences   RETURNS integer as  body    DECLARE sequencedefs RECORD  c integer     BEGIN     FOR sequencedefs IN Select       constraint column usage table name as tablename        constraint column usage table name as tablename         constraint column usage column name as columnname        replace replace columns column default      regclass        nextval         as sequencename       from information schema constraint column usage  information schema columns       where constraint column usage table schema   public  AND        columns table schema    public  AND columns table name constraint column usage table name       AND constraint column usage column name   columns column name       AND columns column default is not null    LOOP           EXECUTE  select max    sequencedefs columnname     from      sequencedefs tablename INTO c        IF c is null THEN c   0  END IF        IF c is not null THEN c   c  1  END IF        EXECUTE  alter sequence      sequencedefs sequencename     restart  with      c     END LOOP      RETURN 1  END   body  LANGUAGE plpgsql   select rebuilt sequences      user457226 solution    --drop function IF EXISTS reset sequence  text text  RESTRICT  CREATE OR REPLACE FUNCTION  reset sequence   tablename text columnname text  RETURNS bigint -- pg catalog   void  AS  body    DECLARE seqname character varying            c integer    BEGIN     select tablename           columnname      seq  into seqname      EXECUTE  SELECT max       columnname        FROM       tablename        into c      if c is null then c   0  end if      c   c 1  --because of substitution of setval with  alter sequence      --EXECUTE  SELECT setval        seqname             cast c as character varying        false    DOES NOT WORK        EXECUTE  alter sequence      seqname     restart with      cast c as character varying       RETURN nextval seqname -1    END   body  LANGUAGE  plpgsql    select sequence name  PG CLASS relname  PG ATTRIBUTE attname         reset sequence PG CLASS relname PG ATTRIBUTE attname  from PG CLASS join PG ATTRIBUTE on PG ATTRIBUTE attrelid   PG CLASS oid join information schema sequences      on information schema sequences sequence name   PG CLASS relname           PG ATTRIBUTE attname      seq  where sequence schema  public

User · Answer

SELECT setval    makes JDBC bork  so here s a Java-compatible way of doing this   -- work around JDBC  A result was returned when none was expected   -- fix broken nextval due to poorly written 20140320100000 CreateAdminUserRoleTables sql DO  BEGIN PERFORM setval pg get serial sequence   admin user role groups      id     1   COALESCE MAX id   0   FALSE  FROM admin user role groups  END

User · Answer

The shortest and fastest way  SELECT setval  tbl tbl id seq   max tbl id   FROM tbl   tbl id being the serial column of table tbl  drawing from the sequence tbl tbl id seq  which is the default automatic name   If you don t know the name of the attached sequence  which doesn t have to be in default form   use pg get serial sequence    SELECT setval pg get serial sequence  tbl    tbl id    max tbl id   FROM tbl   There is no off-by-one error here  The manual   The two-parameter form sets the sequence s last value field to the specified value and sets its is called field to true  meaning that the next nextval will advance the sequence before returning a value   Bold emphasis mine  If the table can be empty and to actually start from 1 in this case  SELECT setval pg get serial sequence  tbl    tbl id                 COALESCE max tbl id    1  1                false  FROM tbl   We can t just use the 2-parameter form and start with 0 because the lower bound of sequences is 1 by default  unless customized   Concurrency To defend against concurrent sequence activity or writes to the table in the above queries  lock the table in SHARE mode  It keeps concurrent transactions from writing a higher number  or anything at all   To also take clients into account that may have fetched sequence numbers in advance without any locks on the main table  yet  can happen in certain setups   only increase the current value of the sequence  never decrease it  It may seem paranoid  but that s in accord with the nature of sequences and defending against concurrency issues  BEGIN   LOCK TABLE tbl IN SHARE MODE   SELECT setval  tbl tbl id seq   max tbl id   FROM   tbl HAVING max tbl id   gt   SELECT last value FROM tbl tbl id seq   -- prevent lower number  COMMIT   SHARE mode is strong enough for the purpose  The manual   This mode protects a table against concurrent data changes   It conflicts with ROW EXCLUSIVE mode   The commands UPDATE  DELETE  and INSERT acquire this lock mode on the target table

User · Answer

-- Login to psql and run the following  -- What is the result  SELECT MAX id  FROM your table   -- Then run    -- This should be higher than the last result  SELECT nextval  your table id seq     -- If it s not higher    run this set the sequence last to your highest id   --  wise to run a quick pg dump first      BEGIN  -- protect against concurrent inserts while you update the counter LOCK TABLE your table IN EXCLUSIVE MODE  -- Update the sequence SELECT setval  your table id seq   COALESCE  SELECT MAX id  1 FROM your table   1   false   COMMIT    Source - Ruby Forum

User · Answer

So I can tell there aren t enough opinions or reinvented wheels in this thread  so I decided to spice things up  Below is a procedure that   is focused  only affects  on sequences that are associated with tables works for both SERIAL and GENERATED AS IDENTITY columns works for good column names and  quot BAD column 123 quot  names automatically assigns the respective sequences  defined start value if the table is empty allows for a specific sequences to be affected only  in schema table column notation  has a preview mode  CREATE OR REPLACE PROCEDURE pg reset all table sequences      IN commit mode BOOLEAN DEFAULT FALSE     IN mask in TEXT DEFAULT NULL   AS    DECLARE     sql reset TEXT      each sec RECORD      new val TEXT  BEGIN  sql reset     sql  SELECT setval pg get serial sequence   1 s  2 s     3 s    coalesce max  quot  3 s quot     4 s   false  FROM  1 s  2 s   sql     FOR each sec IN        SELECT         quote ident table schema  as table schema         quote ident table name  as table name         column name         coalesce identity start  INT  seqstart  as min val     FROM information schema columns     JOIN pg sequence ON seqrelid   pg get serial sequence quote ident table schema        quote ident table name    column name   regclass     WHERE          is identity  boolean OR column default LIKE  nextval    -- catches both SERIAL and IDENTITY sequences      -- mask on column address  schema table column  if supplied     AND coalesce  table schema       table name       column name   mask in  TRUE     LOOP  IF commit mode THEN     EXECUTE format sql reset  each sec table schema  each sec table name  each sec column name  each sec min val  INTO new val      RAISE INFO  Resetting sequence for          to                each sec table schema             each sec table name             each sec column name             new val       ELSE     RAISE INFO  Sequence found for resetting                       each sec table schema             each sec table name             each sec column name       END IF    END LOOP   END    LANGUAGE plpgsql    to preview  call pg reset all table sequences    to commit  call pg reset all table sequences true   to specify only your target table  call pg reset all table sequences  schema table column

User · Answer

SELECT setval    makes JDBC bork  so here s a Java-compatible way of doing this   -- work around JDBC  A result was returned when none was expected   -- fix broken nextval due to poorly written 20140320100000 CreateAdminUserRoleTables sql DO  BEGIN PERFORM setval pg get serial sequence   admin user role groups      id     1   COALESCE MAX id   0   FALSE  FROM admin user role groups  END

User · Answer

pg get serial sequence can be used to avoid any incorrect assumptions about the sequence name  This resets the sequence in one shot   SELECT pg catalog setval pg get serial sequence  table name    id     SELECT MAX id  FROM table name  1     Or more concisely   SELECT pg catalog setval pg get serial sequence  table name    id    MAX id   FROM table name    However this form can t handle empty tables correctly  since max id  is null  and neither can you setval 0 because it would be out of range of the sequence  One workaround for this is to resort to the ALTER SEQUENCE syntax i e   ALTER SEQUENCE table name id seq RESTART WITH 1  ALTER SEQUENCE table name id seq RESTART  -- 8 4 or higher   But ALTER SEQUENCE is of limited use because the sequence name and restart value cannot be expressions   It seems the best all-purpose solution is to call setval with false as the 3rd parameter  allowing us to specify the  next value to use    SELECT setval pg get serial sequence  t1    id    coalesce max id  0    1  false  FROM t1    This ticks all my boxes    avoids hard-coding the actual sequence name  handles empty tables correctly  handles tables with existing data  and does not leave a hole in the sequence   Finally  note that pg get serial sequence only works if the sequence is owned by the column  This will be the case if the incrementing column was defined as a serial type  however if the sequence was added manually it is necessary to ensure ALTER SEQUENCE    OWNED BY is also performed    i e  if serial type was used for table creation  this should all work   CREATE TABLE t1     id serial    name varchar 20      SELECT pg get serial sequence  t1    id    -- returns  t1 id seq   -- reset the sequence  regardless whether table has rows or not  SELECT setval pg get serial sequence  t1    id    coalesce max id  0    1  false  FROM t1    But if sequences were added manually   CREATE TABLE t2     id integer NOT NULL    name varchar 20      CREATE SEQUENCE t2 custom id seq     START WITH 1     INCREMENT BY 1     NO MINVALUE     NO MAXVALUE     CACHE 1   ALTER TABLE t2 ALTER COLUMN id SET DEFAULT nextval  t2 custom id seq   regclass    ALTER SEQUENCE t2 custom id seq OWNED BY t2 id  -- required for pg get serial sequence  SELECT pg get serial sequence  t2    id    -- returns  t2 custom id seq   -- reset the sequence  regardless whether table has rows or not  SELECT setval pg get serial sequence  t2    id    coalesce max id  0    1  false  FROM t1

User · Answer

Reset all sequences  no assumptions about names except that the primary key of each table is  id    CREATE OR REPLACE FUNCTION  reset sequence   tablename text  columnname text  RETURNS  pg catalog   void  AS  body  DECLARE BEGIN     EXECUTE  SELECT setval  pg get serial sequence        tablename                columnname               SELECT COALESCE MAX id  1 1  FROM      tablename        false    END   body   LANGUAGE  plpgsql    select table name           column name      seq   reset sequence table name  column name  from information schema columns where column default like  nextval

User · Answer

If you see this error when you are loading custom SQL data for initialization  another way to avoid this is   Instead of writing   INSERT INTO book  id  name  price  VALUES  1    Alchemist    10     Remove the id  primary key  from initial data  INSERT INTO book  name  price  VALUES   Alchemist    10     This keeps the Postgres sequence in sync

User · Answer

Some really hardcore answers here  I m assuming it used to be really bad at around the time when this has been asked  since a lot of answers from here don t works for version 9 3  The documentation since version 8 0 provides an answer to this very question   SELECT setval  serial   max id   FROM distributors    Also  if you need to take care of case-sensitive sequence names  that s how you do it   SELECT setval   Serial    max id   FROM distributors

User · Answer

-- Login to psql and run the following  -- What is the result  SELECT MAX id  FROM your table   -- Then run    -- This should be higher than the last result  SELECT nextval  your table id seq     -- If it s not higher    run this set the sequence last to your highest id   --  wise to run a quick pg dump first      BEGIN  -- protect against concurrent inserts while you update the counter LOCK TABLE your table IN EXCLUSIVE MODE  -- Update the sequence SELECT setval  your table id seq   COALESCE  SELECT MAX id  1 FROM your table   1   false   COMMIT    Source - Ruby Forum

User · Answer

Reset all sequence from public  CREATE OR REPLACE FUNCTION  reset sequence   tablename text  RETURNS  pg catalog   void  AS   body      DECLARE    BEGIN    EXECUTE  SELECT setval            tablename          id seq              SELECT id   1 FROM          tablename           ORDER BY id DESC LIMIT 1   false        END     body   LANGUAGE  plpgsql    select sequence name  reset sequence split part sequence name    id seq  1   from information schema sequences         where sequence schema  public

User · Answer

This issue happens with me when using entity framework to create the database and then seed the database with initial data  this makes the sequence mismatch   I Solved it by Creating a script to run after seeding the database   DO  do  DECLARE tablename text  BEGIN     -- change the where statments to include or exclude whatever tables you need     FOR tablename IN SELECT table name FROM information schema tables WHERE table schema  public  AND table type  BASE TABLE  AND table name       EFMigrationsHistory          LOOP             EXECUTE format  SELECT setval pg get serial sequence     s       Id      SELECT MAX  Id     1 from   s      tablename  tablename       END LOOP  END  do

User · Answer

Some really hardcore answers here  I m assuming it used to be really bad at around the time when this has been asked  since a lot of answers from here don t works for version 9 3  The documentation since version 8 0 provides an answer to this very question   SELECT setval  serial   max id   FROM distributors    Also  if you need to take care of case-sensitive sequence names  that s how you do it   SELECT setval   Serial    max id   FROM distributors

User · Answer

I spent an hour trying to get djsnowsill s answer to work with a database using Mixed Case tables and columns  then finally stumbled upon the solution thanks to a comment from Manuel Darveau  but I thought I could make it a bit clearer for everyone   CREATE OR REPLACE FUNCTION  reset sequence   tablename text  columnname text  RETURNS  pg catalog   void  AS  body  DECLARE BEGIN EXECUTE format  SELECT setval pg get serial sequence    1 I     2 L            SELECT COALESCE MAX  2 I  1 1  FROM  1 I   false   tablename columnname   END   body   LANGUAGE  plpgsql    SELECT format   s  s seq  table name column name   reset sequence table name column name   FROM information schema columns WHERE column default like  nextval      This has the benefit of    not assuming ID column is spelled a particular way  not assuming all tables have a sequence  working for Mixed Case table column names   using format to be more concise    To explain  the problem was that pg get serial sequence takes strings to work out what you re referring to  so if you do    TableName  --it thinks it s a table or column  TableName  --it thinks it s a string  but makes it lower case   TableName   --it works    This is achieved using    1 I   in the format string     makes an apostrophe 1  means first arg  and I means in quotes

User · Answer

This will reset all sequences from public making no assumptions about table or column names  Tested on version 8 4 CREATE OR REPLACE FUNCTION  quot reset sequence quot   tablename text  columnname text  sequence name text       RETURNS  quot pg catalog quot   quot void quot  AS            body          DECLARE        BEGIN             EXECUTE  SELECT setval         sequence name                 SELECT MAX      columnname                  FROM      tablename             1               END              body   LANGUAGE  plpgsql             SELECT table name           column name      seq        reset sequence table name  column name  table name           column name      seq    FROM information schema columns where column default like  nextval

User · Answer

This answer is a copy from mauro   drop function IF EXISTS rebuilt sequences   RESTRICT  CREATE OR REPLACE FUNCTION  rebuilt sequences   RETURNS integer as  body    DECLARE sequencedefs RECORD  c integer     BEGIN     FOR sequencedefs IN Select       DISTINCT constraint column usage table name  as tablename        constraint column usage column name as columnname        replace replace columns column default      regclass        nextval         as sequencename       from information schema constraint column usage  information schema columns       where constraint column usage table schema   public  AND        columns table schema    public  AND columns table name constraint column usage table name       AND constraint column usage column name   columns column name       AND columns column default is not null        ORDER BY sequencename    LOOP           EXECUTE  select max    sequencedefs columnname     from      sequencedefs tablename INTO c        IF c is null THEN c   0  END IF        IF c is not null THEN c   c  1  END IF        EXECUTE  alter sequence      sequencedefs sequencename     minvalue    c     start      c     restart  with      c     END LOOP      RETURN 1  END   body  LANGUAGE plpgsql   select rebuilt sequences

User · Answer

So I can tell there aren t enough opinions or reinvented wheels in this thread  so I decided to spice things up  Below is a procedure that   is focused  only affects  on sequences that are associated with tables works for both SERIAL and GENERATED AS IDENTITY columns works for good column names and  quot BAD column 123 quot  names automatically assigns the respective sequences  defined start value if the table is empty allows for a specific sequences to be affected only  in schema table column notation  has a preview mode  CREATE OR REPLACE PROCEDURE pg reset all table sequences      IN commit mode BOOLEAN DEFAULT FALSE     IN mask in TEXT DEFAULT NULL   AS    DECLARE     sql reset TEXT      each sec RECORD      new val TEXT  BEGIN  sql reset     sql  SELECT setval pg get serial sequence   1 s  2 s     3 s    coalesce max  quot  3 s quot     4 s   false  FROM  1 s  2 s   sql     FOR each sec IN        SELECT         quote ident table schema  as table schema         quote ident table name  as table name         column name         coalesce identity start  INT  seqstart  as min val     FROM information schema columns     JOIN pg sequence ON seqrelid   pg get serial sequence quote ident table schema        quote ident table name    column name   regclass     WHERE          is identity  boolean OR column default LIKE  nextval    -- catches both SERIAL and IDENTITY sequences      -- mask on column address  schema table column  if supplied     AND coalesce  table schema       table name       column name   mask in  TRUE     LOOP  IF commit mode THEN     EXECUTE format sql reset  each sec table schema  each sec table name  each sec column name  each sec min val  INTO new val      RAISE INFO  Resetting sequence for          to                each sec table schema             each sec table name             each sec column name             new val       ELSE     RAISE INFO  Sequence found for resetting                       each sec table schema             each sec table name             each sec column name       END IF    END LOOP   END    LANGUAGE plpgsql    to preview  call pg reset all table sequences    to commit  call pg reset all table sequences true   to specify only your target table  call pg reset all table sequences  schema table column

User · Answer

This answer is a copy from mauro   drop function IF EXISTS rebuilt sequences   RESTRICT  CREATE OR REPLACE FUNCTION  rebuilt sequences   RETURNS integer as  body    DECLARE sequencedefs RECORD  c integer     BEGIN     FOR sequencedefs IN Select       DISTINCT constraint column usage table name  as tablename        constraint column usage column name as columnname        replace replace columns column default      regclass        nextval         as sequencename       from information schema constraint column usage  information schema columns       where constraint column usage table schema   public  AND        columns table schema    public  AND columns table name constraint column usage table name       AND constraint column usage column name   columns column name       AND columns column default is not null        ORDER BY sequencename    LOOP           EXECUTE  select max    sequencedefs columnname     from      sequencedefs tablename INTO c        IF c is null THEN c   0  END IF        IF c is not null THEN c   c  1  END IF        EXECUTE  alter sequence      sequencedefs sequencename     minvalue    c     start      c     restart  with      c     END LOOP      RETURN 1  END   body  LANGUAGE plpgsql   select rebuilt sequences

User · Answer

Try reindex   UPDATE  As pointed out in the comments  this was in reply to the original question

User · Answer

My version use the first one  with some error checking     BEGIN  CREATE OR REPLACE FUNCTION reset sequence  table schema text   tablename text   columnname text   sequence name text  RETURNS pg catalog void AS  BODY  DECLARE BEGIN  PERFORM 1  FROM information schema sequences  WHERE   sequence schema    table schema AND   sequence name    sequence name   IF FOUND THEN   EXECUTE  SELECT setval          table schema            sequence name                 SELECT MAX       columnname       FROM       table schema            tablename             1     ELSE   RAISE WARNING  SEQUENCE NOT UPDATED ON        tablename   columnname   END IF  END    BODY   LANGUAGE  plpgsql    SELECT reset sequence table schema  table name  column name  table name           column name      seq   FROM information schema columns WHERE column default LIKE  nextval     DROP FUNCTION reset sequence  table schema text   tablename text   columnname text   sequence name text    COMMIT

User · Answer

Ugly hack to fix it using some shell magic  not a great solution but might inspire others with similar problems     pg dump -s  lt DATABASE gt    grep  CREATE TABLE    awk   print  SELECT setval     3   id seq    SELECT MAX id  FROM    3           sed  s     g    psql  lt DATABASE gt  -f -

User · Answer

I suggest this solution found on postgres wiki  It updates all sequences of your tables   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    How to use from postgres wiki     Save this to a file  say  reset sql  Run the file and save its output in a way that doesn t include the usual headers  then run that output  Example    Example   psql -Atq -f reset sql -o temp psql -f temp rm temp   Original article also with fix for sequence ownership  here

User · Answer

ALTER SEQUENCE sequence name RESTART WITH  SELECT max id  FROM table name   Doesn t work   Copied from  tardate answer   SELECT setval pg get serial sequence  table name    id    MAX id   FROM table name

User · Answer

Recheck all sequence in public schema function  CREATE OR REPLACE FUNCTION public recheck sequence     RETURNS void AS  body  DECLARE    table name VARCHAR     column name VARCHAR       sequence name VARCHAR  BEGIN   FOR  table name IN SELECT tablename FROM pg catalog pg tables WHERE schemaname    public  LOOP     FOR  column name IN SELECT column name FROM information schema columns WHERE table name    table name LOOP         SELECT pg get serial sequence  table name   column name  INTO  sequence name          IF  sequence name IS NOT NULL THEN              EXECUTE  SELECT setval       sequence name       COALESCE  SELECT MAX    quote ident  column name      1 FROM    quote ident  table name       1   FALSE             END IF      END LOOP       END LOOP  END   body  LANGUAGE  plpgsql  VOLATILE CALLED ON NULL INPUT SECURITY INVOKER COST 100

[postgresql] How to reset postgres' primary key sequence when it falls out of sync?

Examples related to postgresql

Examples related to primary-key

Examples related to database-sequence