11:06

Find the value of all database sequences

26 September 2018

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:

CREATE OR REPLACE FUNCTION sequence_values() RETURNS TABLE(name text, value bigint) AS $sequence_values$
DECLARE
   nsp_name TEXT;
   seq_name TEXT;
BEGIN
   FOR nsp_name, seq_name IN
       SELECT nspname::text, relname::text
          FROM pg_class 
          JOIN pg_namespace
          ON pg_class.relnamespace = pg_namespace.oid WHERE relkind='S'
   LOOP
       RETURN QUERY EXECUTE 'SELECT ''' || nsp_name || '.' || seq_name || '''::text, last_value FROM "' || nsp_name || '"."' || seq_name || '"';
   END LOOP;
END;
$sequence_values$
LANGUAGE plpgsql;

There are 2 comments.

David Fetter at 10:21, 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 at 19:19, 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.