Subject: How to find all current sequence IDs

From: "Campbell, Lance" <[email-masked]>
Date: Wed, 21 Feb 2024 22:06:55 +0000

Is there a way to get a list of all sequences, the schema it is used in, and the current sequence number in use?

The below SQL will give me the schema and sequences. It does not give me the current sequence number in use.

SELECT sequence_schema, sequence_name
FROM information_schema.sequences
ORDER BY sequence_name ;

Thanks,

Lance Campbell
University of Illinois

---

From: Tom Lane <[email-masked]>
Date: Wed, 21 Feb 2024 17:13:56 -0500

> Is there a way to get a list of all sequences, the schema it is used in, and the current sequence number in use?

Try something like

select relname, relnamespace::regnamespace, pg_sequence_last_value(oid)
from pg_class where relkind = 'S';

			regards, tom lane

---

From: Nisarg Patel <[email-masked]>
Date: Wed, 21 Feb 2024 18:17:15 -0500

This has worked for me in the past:
select schemaname, sequencename, last_value
from pg_sequences;

Thank you,
Nisarg

---

From: "Campbell, Lance" <[email-masked]>
Date: Thu, 29 Feb 2024 14:49:26 +0000

That worked great. Thanks so much.

From: Nisarg Patel <[email-masked]>
Sent: Wednesday, February 21, 2024 5:17 PM
To: Tom Lane <[email-masked]>
Cc: Campbell, Lance <[email-masked]>; [email-masked]
Subject: Re: How to find all current sequence IDs

Thank you,
Nisarg
On Wed, Feb 21, 2024, 5:14 PM Tom Lane <[email-masked]<mailto:[email-masked]>> wrote:

Try something like

                        regards, tom lane