Sequence exists but I can't find it in information_schema.sequences (PostgreSQL)

Question

I have created a sequence (let's call it my_seq) in a schema (let's call it my_schema) of my PostgreSQL (version 13) database. I am sure the sequence exists because I can find it in the result set of the query

select n.nspname as sequence_schema, 
       c.relname as sequence_name
from pg_class c 
  join pg_namespace n on n.oid = c.relnamespace
where c.relkind = 'S'
and n.nspname = 'my_schema'

However, if I run the following query

select sequence_name 
from information_schema.sequences 
where sequence_schema = 'my_schema'

my_seq isn't in the result set. I have run both queries with the same user I created the sequence with.

Can anybody help me find an explanation for this?

JGH · Answer 1 · 2021-06-16T14:12:14.850

The missing sequences are likely the ones used in an Identity column.

You can fetch all sequences using select * from pg_sequences;

To answer the why of the question: information_schema.sequences is a view, you can see its definition by running \d+ information_schema.sequences. There, we can see that it filters out objects being an internal dependency (AND NOT (EXISTS ... AND pg_depend.deptype = 'i'), which is the case of the sequences backing an Identity column.

Sequence exists but I can't find it in information_schema.sequences (PostgreSQL)

1 Answers1