23

I want to get the list of sequence names in Postgres.

In Oracle, I can use:

select sequence_name 
from user_sequences

But in Postgres when I use that statement it always throws the error: not found user_sequences.

How can I do this in Postgres?

2 Answers 2

42

You can use:

select sequence_schema, sequence_name
from information_schema.sequences;

That will return a list of sequences accessible to the current user, not the ones owned by him.

If you want to list sequences owned by the current user you need to join pg_class, pg_namespace and pg_user:

select n.nspname as sequence_schema, 
       c.relname as sequence_name,
       u.usename as owner
from pg_class c 
  join pg_namespace n on n.oid = c.relnamespace
  join pg_user u on u.usesysid = c.relowner
where c.relkind = 'S'
  and u.usename = current_user;

In Postgres a user can own objects (e.g. sequences) in multiple schemas, not just "his own", so you also need to check in which schema the sequence is created.

More details in the manual:

Sign up to request clarification or add additional context in comments.

Comments

18

SELECT c.relname FROM pg_class c WHERE c.relkind = 'S';
Or trying running psql as psql -U username -E followed by \ds. This will show you the query that was been used to generate the result as well.

4 Comments

I want to get this like the Oracle,through sql statement to select the sequence name.
About this question in sqlsever,How can I do it ?"SELECT * FROM SYS.SEQUENCES" can not get anything .
@liu246437 I am not too sure about this as I have not use sqlserver before. Maybe you can post a new question with the relevant tags so that the other professionals can help you better.
@liu246437 Note that this will show sequences accessible to the current user, not the ones owned by the current user (which is what user_sequences in Oracle does)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.