Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-admin-owner+M41874=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1VhPCA-00037W-2v
 for pgsql-admin@arkaria.postgresql.org; Fri, 15 Nov 2013 19:36:46 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-admin-owner+M41874=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1VhPC9-0001gV-I6
 for pgsql-admin@arkaria.postgresql.org; Fri, 15 Nov 2013 19:36:45 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <yields.falsehood@gmail.com>) id 1VhPC7-0001dv-Fo
 for pgsql-admin@postgresql.org; Fri, 15 Nov 2013 19:36:43 +0000
Received: from mail-qc0-x22e.google.com ([2607:f8b0:400d:c01::22e])
 by magus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <yields.falsehood@gmail.com>) id 1VhPC4-0007sq-Mz
 for pgsql-admin@postgresql.org; Fri, 15 Nov 2013 19:36:43 +0000
Received: by mail-qc0-f174.google.com with SMTP id v2so2418574qcr.33
 for <pgsql-admin@postgresql.org>; Fri, 15 Nov 2013 11:36:39 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
 h=message-id:date:from:user-agent:mime-version:to:subject:references
 :in-reply-to:content-type;
 bh=x3rYla+RBMWj2UTCnOW/3PqlCb1Eb2ClCGBdjSOferc=;
 b=kdgerlW6l2sfQe2ydUcyIHakzHYgsCxZ6qFWzcV8VtHfUuBCKTuvZiEeDVtAn/a/uo
 xDgQvENkMKDxmvSVJzbAkdac5/nw0pKFTIjFQB+bes9ldLe5zdQxMRpPR0E9fucp3B+N
 i6NxgD1IAiq2eO/AaWHeDk0IGiPJ/+MqIlJy4tMVDKZQjcnG+IF+FekJ2FOIq+dXaweK
 5mUv0ufVShEHQbTdz4GLntjHAfQPD7qAr6UbnKz8bXy9fDh8C8czPVPITEe/1TdaYvNJ
 WqQAaCKvIWhRcj+awJuUOa4drHLyX6Hl0Gf6S3hijOWL6TQF/W0CcGBVBo8bsIc9MftT
 dbcA==
X-Received: by 10.49.120.72 with SMTP id la8mr13497603qeb.62.1384544199376;
 Fri, 15 Nov 2013 11:36:39 -0800 (PST)
Received: from [192.168.204.25]
 (173-161-251-161-Philadelphia.hfc.comcastbusiness.net. [173.161.251.161])
 by mx.google.com with ESMTPSA id b10sm8839064qeg.7.2013.11.15.11.36.38
 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Fri, 15 Nov 2013 11:36:38 -0800 (PST)
Message-ID: <528677BF.7060308@gmail.com>
Date: Fri, 15 Nov 2013 14:36:31 -0500
From: Elliot <yields.falsehood@gmail.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:24.0) Gecko/20100101 Thunderbird/24.1.0
MIME-Version: 1.0
To: Thara Vadakkeveedu <tharagv@yahoo.com>,
 "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: checking if sequence exists
References: 
 <CA+yMuNq+unyKgCrpfmmb2WK0=toMzqrx7491n=upM8Rrj3KtwQ@mail.gmail.com>
	<CAKoxK+5DvJYF3tor6=kJKdfhpxoui6PAFjuhy9WzHjZbngGDPQ@mail.gmail.com>
 <CA+yMuNqxZu+xZeGiD-fdC+QZK-x9+zg03X8xzDpBvQcVTncYxg@mail.gmail.com>
 <1384543840.72953.YahooMailNeo@web125002.mail.ne1.yahoo.com>
In-Reply-To: <1384543840.72953.YahooMailNeo@web125002.mail.ne1.yahoo.com>
Content-Type: multipart/alternative;
 boundary="------------020200040603000803000801"
X-Pg-Spam-Score: -2.0 (--)
List-Archive: <http://archives.postgresql.org/pgsql-admin>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-admin.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@postgresql.org>
List-Post: <mailto:pgsql-admin@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-admin>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
X-Mailing-List: pgsql-admin
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

This is a multi-part message in MIME format.
--------------020200040603000803000801
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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
;


--------------020200040603000803000801
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <div class="moz-cite-prefix">On 2013-11-15 14:30, Thara Vadakkeveedu
      wrote:<br>
    </div>
    <blockquote
      cite="mid:1384543840.72953.YahooMailNeo@web125002.mail.ne1.yahoo.com"
      type="cite">
      <div style="color:#000; background-color:#fff;
        font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial,
        Lucida Grande, sans-serif;font-size:14pt">
        <div style="RIGHT: auto"><span style="RIGHT: auto">How can we
            find out if a particular sequence exists ? The idea is to
            check if sequence&nbsp;first and if it does not exist then create
            it...the goal is to do this when we deploy the application
            war...</span></div>
        <div style="RIGHT: auto"><span style="RIGHT: auto">thanks</span></div>
        <div style="RIGHT: auto"><span style="RIGHT: auto">tg</span></div>
        <div style="RIGHT: auto"><br>
          &nbsp;</div>
      </div>
    </blockquote>
    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.<br>
    <br>
    For instance,<br>
    <br>
    These steps:<br>
    \set ECHO_HIDDEN 1<br>
    \ds<br>
    <br>
    Yield a query like this:<br>
    SELECT n.nspname as "Schema",<br>
    &nbsp; c.relname as "Name",<br>
    &nbsp; 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",<br>
    &nbsp; pg_catalog.pg_get_userbyid(c.relowner) as "Owner"<br>
    FROM pg_catalog.pg_class c<br>
    &nbsp;&nbsp;&nbsp;&nbsp; LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace<br>
    WHERE c.relkind IN ('S','s','')<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND n.nspname !~ '^pg_toast'<br>
    &nbsp; AND n.nspname = '&lt;schemaname&gt;'<br>
    &nbsp; and c.relname = '&lt;sequencename&gt;'<br>
    ORDER BY 1,2<br>
    ;<br>
    <br>
  </body>
</html>

--------------020200040603000803000801--