Upon occasion, you want to get the current value of all the sequences in the database. For example, you may have fields that are integer rather than bigint, and you’re concerned how close you are to overflowing one of them (since sequences are bigint and will happily crash through the size of a 32-bit integer).
Unfortunately, currval() requires that you have actually accessed the sequence value using nextval() in the current session… but you don’t want to increment the value, just test it!
Here’s a cheap and cheerful PL/pgSQL function that returns all of the current sequence values:
1 CREATE OR REPLACE FUNCTION sequence_values() RETURNS TABLE(name text, value bigint) AS $sequence_values$ 2 DECLARE 3 nsp_name TEXT; 4 seq_name TEXT; 5 BEGIN 6 FOR nsp_name, seq_name IN 7 SELECT nspname::text, relname::text 8 FROM pg_class 9 JOIN pg_namespace 10 ON pg_class.relnamespace = pg_namespace.oid WHERE relkind='S' 11 LOOP 12 RETURN QUERY EXECUTE 'SELECT ''' || nsp_name || '.' || seq_name || '''::text, last_value FROM "' || nsp_name || '"."' || seq_name || '"'; 13 END LOOP; 14 END; 15 $sequence_values$ 16 LANGUAGE plpgsql;
Comments
David Fetter · 27 September 2018
Since format() came out, I've been leaning on it heavily for query generation. Hostile names for objects are unlikely, but ones with spaces in them can cause headaches. CREATE OR REPLACE FUNCTION sequence_values() RETURNS TABLE(sequence_name TEXT, sequence_value BIGINT) LANGUAGE plpgsql AS $$ DECLARE q TEXT; BEGIN FOR q IN SELECT format($q$SELECT I%.%I::text, last_value FROM %1$I.%1$I$q$, n.nspname, c.relname) FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON ( c.relkind = 'S' AND c.relnamespace = n.oid ) LOOP RETURN QUERY EXECUTE q; END LOOP; END; $$;Patrick TJ McPhee · 27 September 2018
I think it's worth noting postgres 10 has a view pg_sequences, which nearly gives this information: select schemaname || '.' || sequencename, last_value from pg_sequences ; one caveat is that last_value is only shown if the sequence's is_called flag is set.