Get all sequences with current values

Question

I have the following query that gets all sequences and their schemas:

SELECT sequence_schema as schema, sequence_name as sequence
FROM information_schema.sequences
WHERE sequence_schema NOT IN ('topology', 'tiger')
ORDER BY 1, 2

I would like to get the current value of each sequence name with something like select last_value from [sequence];. I have tried the following (and a couple variations), but it doesn't work because the syntax isn't correct:

DO $$
BEGIN
    EXECUTE 
        sequence_schema as schema,
        sequence_name as sequence,
        last_value
    FROM information_schema.sequences
    LEFT JOIN (
        EXECUTE 'SELECT last_value FROM ' || schema || '.' || sequence
    ) tmp
    ORDER BY 1, 2;
END
$$;

I've found some solutions that create functions to execute text or piece together a query inside a function and return the result, but I would prefer to have a single query that I can run and modify however I like.

score 21 · Accepted Answer · answered Jun 23 '20 at 14:13

21

In Postgres 12, you can use pg_sequences:

select schemaname as schema, 
       sequencename as sequence, 
       last_value
from pg_sequences

answered Jun 23 '20 at 14:13

score 4 · Answer 2 · answered Jun 23 '20 at 13:03

4

You can rely on the function pg_sequence_last_value

SELECT nspname as schema, 
       relname AS sequence_name,
       coalesce(pg_sequence_last_value(s.oid), 0) AS seq_last_value
FROM pg_class AS s
   JOIN pg_depend AS d ON d.objid = s.oid
   JOIN pg_attribute a ON d.refobjid = a.attrelid
                          AND d.refobjsubid = a.attnum
   JOIN pg_namespace nsp ON s.relnamespace = nsp.oid
WHERE s.relkind = 'S'
  AND d.refclassid = 'pg_class'::regclass
  AND d.classid = 'pg_class'::regclass
  AND nspname NOT IN ('topology', 'tiger')
ORDER BY 1,2 DESC;

answered Jun 23 '20 at 13:03

JGH

15,928
4
31
48

Can you provide a link to that function? I don't think it's a standard Postgres function – Jun 23 '20 at 13:07
1

@a_horse_with_no_name I believe it is... mine is located in pg_catalog and I wouldn't add anything there myself. While I couldn't find it in the doc, here is another [reference](https://www.citusdata.com/blog/2018/06/14/scalable-incremental-data-aggregation/) to it. And here is the [code](https://github.com/postgres/postgres/blob/7ce461560159948ba0c802c767e42c5f5ae08b4a/src/backend/commands/sequence.c#L1847) – JGH Jun 23 '20 at 13:17
1

Then it seems it's not documented – Jun 23 '20 at 13:28
This mailing list says the function shouldn't be called directly, but there is a view (`pg_catalog.pg_sequences.last_value`) that uses the function: https://www.postgresql.org/message-id/9246.1569282009%40sss.pgh.pa.us – GammaGames Jun 23 '20 at 14:12
1

A little more explanation, the function name is misleading - its not the last *issued* value, but rather the last *saved/cached* value to disk. Not to be trusted for consumption. – Alexi Theodore Jun 14 '23 at 19:08

score 2 · Answer 3 · answered Sep 09 '21 at 22:01

Here's a solution that doesn't rely on pg_sequences or pg_sequence_last_value:

    CREATE OR REPLACE FUNCTION get_sequences()
      RETURNS TABLE (
        last_value bigint,
        sequence_schema text,
        sequence_name text
      )
      LANGUAGE plpgsql AS
    $func$
    DECLARE
        s RECORD;
    BEGIN
        FOR s IN SELECT t.sequence_schema, t.sequence_name
               FROM information_schema.sequences t
        LOOP
          RETURN QUERY EXECUTE format(
              'SELECT last_value, ''%1$s''::text, ''%2$s''::text FROM %1$I.%2$I',
              s.sequence_schema,
              s.sequence_name
          );
        END LOOP;
    END;
    $func$;

    SELECT * FROM get_sequences();

That'll output a table like this:

 last_value | sequence_schema |                     sequence_name
------------+-----------------+-------------------------------------------------------
          1 | public          | contact_infos_id_seq
          1 | media           | photos_id_seq
       2006 | company         | companies_id_seq
       2505 | public          | houses_id_seq
          1 | public          | purchase_numbers_id_seq
... etc

The other answers will only work if you are on a modern version of Postgres (I believe 10 or greater).

Get all sequences with current values

3 Answers3