Re: checking if sequence exists

[Elliot <yields.falsehood@xxxxxxxxx>]

On 2013-11-15 14:30, Thara Vadakkeveedu wrote:

How can we find out if a particular sequence exists ? The idea is to check if sequence first and if it does not exist then create it...the goal is to do this when we deploy the application war...

thanks

tg

 

In psql if you set ECHO_HIDDEN you can get it to dump out its introspection queries, like the one for \ds, which gives you a list of sequences.

For instance,

These steps:
\set ECHO_HIDDEN 1
\ds

Yield a query like this:
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('S','s','')
      AND n.nspname !~ '^pg_toast'
  AND n.nspname = '<schemaname>'
  and c.relname = '<sequencename>'
ORDER BY 1,2
;