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.
In Postgres 12, you can use pg_sequences:
select schemaname as schema,
sequencename as sequence,
last_value
from pg_sequences
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;
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).