Fix postgres sequence after insert
Run the output of this sql command
SELECT 'SELECT SETVAL(' || quote_literal(quote_ident(sequence_namespace.nspname) || '.' || quote_ident(class_sequence.relname)) || ', COALESCE(MAX(' ||quote_ident(pg_attribute.attname)|| '), 1) ) FROM ' || quote_ident(table_namespace.nspname)|| '.'||quote_ident(class_table.relname)|| ';' 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;Alternatives List of all sequence
SELECT c.relname FROM pg_class c WHERE c.relkind = 'S' order BY c.relname;Fix sequence (if it's named id)
SELECT concat('SELECT SETVAL(''public.', c.relname,''', COALESCE(MAX(id), 1) ) FROM public.', replace(c.relname, '_id_seq', ''), ';') FROM pg_class c WHERE c.relkind = 'S' order BY c.relname;
4 марта 2024, 17:26