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

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

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;

In the example below, the table name is users and the schema name is public (default schema), replace it according to your needs.

1. Check the max id:

SELECT MAX(id) FROM public.users;

2. Check the next value:

SELECT nextval('public."users_id_seq"');

3. If the next value is lower than the max id, reset it:

SELECT setval('public."users_id_seq"',
  (SELECT MAX(id) FROM public.users)
);

Note:

nextval() will increment the sequence before returning the current value while currval() would just return the current value, as documented here.

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

Just run below command:

SELECT setval('my_table_seq', (SELECT max(id) FROM my_table));

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

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 "BAD_column_123" 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("%3$s"), %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');

Try reindex.

UPDATE: As pointed out in the comments, this was in reply to the original question.

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

I couldn't find an answer explicitly for Rails.

From the rails console

ActiveRecord::Base.connection.execute("SELECT setval(pg_get_serial_sequence('table_name', 'id'), MAX(id)) FROM table_name;")

Replacing table_name with users for example.