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

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.

This version of the postgres wiki article correctly resets the next-id value for a sequence even if there are no rows in the the table.

It also resets the cache for the next id value, which the postgres wiki article ignored.

DO
$do$
DECLARE
   _sql text;
BEGIN
  FOR _sql IN
     SELECT
        'SELECT SETVAL(' ||
           quote_literal(quote_ident(sequence_namespace.nspname) || '.' || quote_ident(class_sequence.relname)) ||
           ', (SELECT COALESCE(MAX(' ||quote_ident(pg_attribute.attname)|| ') + 1, 1) FROM ' ||
           quote_ident(table_namespace.nspname)|| '.'||quote_ident(class_table.relname)|| '), FALSE );'
    FROM pg_depend
        INNER JOIN pg_class AS class_sequence
            ON class_sequence.oid = pg_depend.objid
                AND class_sequence.relkind = 'S'
        INNER JOIN pg_class AS class_table
            ON class_table.oid = pg_depend.refobjid
        INNER JOIN pg_attribute
            ON pg_attribute.attrelid = class_table.oid
                AND pg_depend.refobjsubid = pg_attribute.attnum
        INNER JOIN pg_namespace as table_namespace
            ON table_namespace.oid = class_table.relnamespace
        INNER JOIN pg_namespace AS sequence_namespace
            ON sequence_namespace.oid = class_sequence.relnamespace
    ORDER BY sequence_namespace.nspname, class_sequence.relname
  LOOP
    EXECUTE _sql;
  END LOOP;
END
$do$;

The most generic approach that handles multiple schema's camelcase and other stuff is probably. Something like (Python psydo code):

table_name = "my_table"
id_name = "id"
seq_name = f"{table_name}_{id_name}_seq"
schema_name = "public"

print(f"""SELECT setval('{schema_name}."{seq_name}"',
(SELECT GREATEST(MAX("{id_name}")+1,nextval('{schema_name}."{seq_name}"')) 
FROM {schema_name}."{table_name}"));""")

I had to make a big migration from a non-tenant based database architecture to a tenant based architecture. So I can tell that this is tested :)

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