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

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:

1. avoids hard-coding the actual sequence name
2. handles empty tables correctly
3. 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;

The shortest and fastest way

SELECT setval('tbl_tbl_id_seq', max(tbl_id)) FROM tbl;

tbl_id being the serial or IDENTITY column of table tbl, drawing from the sequence tbl_tbl_id_seq (resulting default name). See:

• Auto increment table column

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() (works for IDENTITY, too):

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

Safe under concurrent write load

To also defend against concurrent sequence activity or writes, 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. That 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) > (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.

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

This will reset all sequences from public making no assumptions about table or column names. Tested on version 8.4

CREATE OR REPLACE FUNCTION "reset_sequence" (tablename text, columnname text, sequence_name text) 
    RETURNS "pg_catalog"."void" 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%';