Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to get list of sequence names in Postgres?

Tags:

postgresql

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?

like image 855
liu246437 Avatar asked Jul 05 '16 02:07

liu246437


People also ask

How do I get a list of column names in PostgreSQL?

To list down all tables columns on a specific table in the a PostgreSQL database using psql command-line, you can use \dS your_table_name.

What is sequence name in PostgreSQL?

A sequence in PostgreSQL is a user-defined schema-bound object that generates a sequence of integers based on a specified specification. To create a sequence in PostgreSQL, you use the CREATE SEQUENCE statement.


1 Answers

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:

  • https://www.postgresql.org/docs/current/static/infoschema-sequences.html
  • https://www.postgresql.org/docs/current/static/catalog-pg-class.html
like image 59
a_horse_with_no_name Avatar answered Sep 28 '22 18:09

a_horse_with_no_name