How to view sequence details with a SQL

Question

As you know, we can view the details of a sequence by using '\d' command. I would like to know if there is a way to get these information with a SQL query so that I can view the details for all sequences in PostgreSQL database.

Eventually, the left 'Column' will be displayed horizontally like we see normally when we use a sql statement.

postgres=# \d next_empno
         Sequence "public.next_empno"
    Column     |  Type   |        Value        
---------------+---------+---------------------
 sequence_name | name    | next_empno
 last_value    | bigint  | 8000
 start_value   | bigint  | 8000
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 0
 is_cycled     | boolean | f
 is_called     | boolean | f

You can always \ds *.*, but otherwise, use the information_schema — Craig Ringer
– Craig Ringer, Commented Jul 31, 2016 at 2:41
I didn't know about information_schema. It has everything I need to check. Thank you. — Sigularity
– Sigularity, Commented Jul 31, 2016 at 4:05
A good way to find those queries is to start psql with the -E (echo hidden) option. It will show you exactly what queries psql uses internally — user330315
– user330315, Commented Jul 31, 2016 at 7:08
Oh. Really wonderful!. Awesome! With that option, we can find the internal queries very easily. Cool. — Sigularity
– Sigularity, Commented Jul 31, 2016 at 7:23
Though note that psql uses the PostgreSQL catalogs directly. Unlike information_schema these are subject to change from version to version and are not standard across database engines. Where possible, you should prefer the information_schema. — Craig Ringer
– Craig Ringer, Commented Aug 1, 2016 at 0:14

allkenang · Accepted Answer · 2019-08-14 08:44:49Z

14

To view the DDL of the sequence, use this

select * from information_schema.sequences where sequence_name='<your_sequence_name_in_lower_case>'

answered Aug 14, 2019 at 8:44

allkenang

1,65516 silver badges15 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2016-07-31 02:08:03Z

8

If I understand correctly, you can use `INFORMATION_SCHEMA.sequences.

There documentation is here.

answered Jul 31, 2016 at 2:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

Panama Jack Over a year ago

It would have been nice if you actually put an example query. I don't find this any more useful than the first comment on the OPs question.

Gordon Linoff Over a year ago

@PanamaJack . . . This answer was 33 minutes before the first comment.

Rogério Viana · Accepted Answer · 2021-05-23 16:26:18Z

1

SQL for view 'sequences':

SELECT * FROM information_schema.sequences

answered May 23, 2021 at 16:26

Rogério Viana

1802 silver badges6 bronze badges

1 Comment

jdow Over a year ago

the better question is how to list them with their currval()

Collectives™ on Stack Overflow

How to view sequence details with a SQL

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related