Postgresql query to fetch sequence metadata

Question

I'm looking for query which will give the complete metadata of sequence.

When I tried in console with the below query I got the metadata.

\d+ phonebook_id_seq

           Sequence "public.phonebook_id_seq"
    Column     |  Type   |        Value        | Storage
---------------+---------+---------------------+---------
 sequence_name | name    | phonebook_id_seq    | plain
 last_value    | bigint  | 2                   | plain
 start_value   | bigint  | 1                   | plain
 increment_by  | bigint  | 1                   | plain
 max_value     | bigint  | 9223372036854775807 | plain
 min_value     | bigint  | 1                   | plain
 cache_value   | bigint  | 1                   | plain
 log_cnt       | bigint  | 31                  | plain
 is_cycled     | boolean | f                   | plain
 is_called     | boolean | t                   | plain

I'm looking for query which will give same output.

I tried below, however I'm not getting the complete metadata info as above.

I tried this query:

SELECT * 
FROM information_schema.sequences 
WHERE sequence_name = 'phonebook_id_seq';

Output:

'postgres','public','phonebook_id_seq','bigint',64,2,0,'1','1','9223372036854775807','1','NO'

if you want to get the query behind the psql meta-command start psql with -E key. Also with postgres 10 the query might have changed I believe as they introduced pg_sequence — Vao Tsun
– Vao Tsun, Commented Jan 30, 2018 at 10:21

Vao Tsun · Accepted Answer · 2018-01-30 10:33:20Z

2

for pre 10 releases (your looks alike), use

select * from phonebook_id_seq

for 10 release use

select * from pg_sequence where seqrelid='phonebook_id_seq'::regclass;

Also https://www.postgresql.org/docs/current/static/app-psql.html

-E

--echo-hidden Echo the actual queries generated by \d and other backslash commands. You can use this to study psql's internal operations. This is equivalent to setting the variable ECHO_HIDDEN to on.

answered Jan 30, 2018 at 10:33

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

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

Collectives™ on Stack Overflow

Postgresql query to fetch sequence metadata

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related