Answer a question

I have used this below query to get the complete information of Sequence objects from the Postgresql catalog table

select s.sequence_name, s.start_value, s.minimum_value, s.maximum_value, s.increment, s.cycle_option 
from information_schema.sequences s 
 where s.sequence_schema='schema1' 

One more attribute value am not able to get is "Cache" value.

Am using Postgresql 9.2

Here is the DDL syntax for the sequence with cache,

ALTER SEQUENCE [ IF EXISTS ] name [ INCREMENT [ BY ] increment ]

[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]

[ START [ WITH ] start ]

[ RESTART [ [ WITH ] restart ] ]

[ CACHE cache ] [ [ NO ] CYCLE ]

[ OWNED BY { table_name.column_name | NONE } ]

Is there any Postgres functions to get this Sequence cache value ?

Thanks,

Ravi

Answers

With PostgreSQL 10 or newer, the cache size can be obtained from the system view pg_sequences or the system table pg_sequence:

SELECT cache_size FROM pg_catalog.pg_sequences
 WHERE schemaname='public' and sequencename='s';

or alternatively

SELECT seqcache FROM pg_catalog.pg_sequence
  WHERE seqrelid = 'public.s'::regclass;

Omit the schema qualification (public or more generally the name of the schema) in the 2nd query to use automatically search_path instead of a fixed schema.

With versions older than v10, you may query the sequence itself as if it was a table.

For example:

CREATE SEQUENCE s CACHE 10;
SELECT cache_value FROM s;

Result:

 cache_value 
-------------
          10

Or

\x
SELECT * FROM s;

Result:

-[ RECORD 1 ]-+-------------------- sequence_name | s last_value | 1 start_value | 1 increment_by | 1 max_value | 9223372036854775807 min_value | 1 cache_value | 10 log_cnt | 0 is_cycled | f is_called | f