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, Jan 30 '18 at 10:21

score 2 · Answer 1 · answered Jan 30 '18 at 10:33

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.

Postgresql query to fetch sequence metadata

1 Answers1