Thread: SQL equivallent to "\ds" in psql

SQL equivallent to "\ds" in psql

From

Arcady Genkin

Date:

17 October 2001, 19:40:21

Where does Postgres store information about the sequences?  I tried
looking in the tables produced by "\dS", but could find no references
to the sequences. :(

What I want is an SQL equivallent of "\ds" command, which will return
a list of all sequences in the current database.  I need this for a
periodically run script so that I could keep an eye on all sequences
in the database.

Many thanks,
--
Arcady Genkin

Re: SQL equivallent to "\ds" in psql

From

"Mitch Vincent"

Date:

17 October 2001, 20:50:20

Use  psql -E and it will give you the SQL for the backslash commands..

-Mitch

----- Original Message -----
From: "Arcady Genkin" <a.genkin@utoronto.ca>
To: "PostgreSQL" <pgsql-general@postgresql.org>
Sent: Wednesday, October 17, 2001 4:35 PM
Subject: [GENERAL] SQL equivallent to "\ds" in psql


> Where does Postgres store information about the sequences?  I tried
> looking in the tables produced by "\dS", but could find no references
> to the sequences. :(
>
> What I want is an SQL equivallent of "\ds" command, which will return
> a list of all sequences in the current database.  I need this for a
> periodically run script so that I could keep an eye on all sequences
> in the database.
>
> Many thanks,
> --
> Arcady Genkin
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

Re: SQL equivallent to "\ds" in psql

From

"Creager, Robert S"

Date:

17 October 2001, 20:50:22

execute psql with a -E option, then do \ds.  You'll see the queries sent to
the backend.

Later,
Rob

> -----Original Message-----
> From: Arcady Genkin [mailto:a.genkin@utoronto.ca]
> Sent: Wednesday, October 17, 2001 2:35 PM
> To: PostgreSQL
> Subject: [GENERAL] SQL equivallent to "\ds" in psql
>
>
> Where does Postgres store information about the sequences?  I tried
> looking in the tables produced by "\dS", but could find no references
> to the sequences. :(
>
> What I want is an SQL equivallent of "\ds" command, which will return
> a list of all sequences in the current database.  I need this for a
> periodically run script so that I could keep an eye on all sequences
> in the database.
>
> Many thanks,
> --
> Arcady Genkin
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

Re: SQL equivallent to "\ds" in psql

From

Jason Earl

Date:

17 October 2001, 21:20:21

If you start psql with the -E flag it will output the
SQL queries it uses to do the nifty \ds command (and
all of the other \ commands).  This is a very handy
tool.

Here's what I get on my test system:

processdata=# \ds
********* QUERY *********
SELECT c.relname as "Name",
  (CASE WHEN relkind = 'S' THEN 'sequence'::text ELSE
'index'::text END) as "Type",
  u.usename as "Owner"
FROM pg_class c, pg_user u
WHERE c.relowner = u.usesysid AND relkind in ('S')
  AND c.relname !~ '^pg_'
UNION
SELECT c.relname as "Name",
  (CASE WHEN relkind = 'S' THEN 'sequence'::text ELSE
'index'::text END) as "Type",
  NULL as "Owner"
FROM pg_class c
WHERE not exists (select 1 from pg_user where usesysid
= c.relowner) AND relkind in ('S')
  AND c.relname !~ '^pg_'

ORDER BY "Name"
*************************

                List of relations
           Name            |   Type   |  Owner
---------------------------+----------+----------
 batterbatches_batchid_seq | sequence | earlj
 board_event_seq           | sequence | postgres
 field_event_seq           | sequence | postgres
 peeler_inst_seq           | sequence | postgres
 prod_journal_id_seq       | sequence | postgres
 raw_inst_seq              | sequence | postgres
 setting_inst_seq          | sequence | postgres
 setting_mods_seq          | sequence | postgres
 setting_specs_seq         | sequence | postgres
 train_event_seq           | sequence | postgres
 tscales_seq               | sequence | postgres
(11 rows)

Take care,
Jason

--- Arcady Genkin <a.genkin@utoronto.ca> wrote:
> Where does Postgres store information about the
> sequences?  I tried
> looking in the tables produced by "\dS", but could
> find no references
> to the sequences. :(
>
> What I want is an SQL equivallent of "\ds" command,
> which will return
> a list of all sequences in the current database.  I
> need this for a
> periodically run script so that I could keep an eye
> on all sequences
> in the database.
>
> Many thanks,
> --
> Arcady Genkin
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html


__________________________________________________
Do You Yahoo!?
Make a great connection at Yahoo! Personals.
http://personals.yahoo.com

Re: SQL equivallent to "\ds" in psql

From

"Brett W. McCoy"

Date:

18 October 2001, 03:20:36

On 17 Oct 2001, Arcady Genkin wrote:

> What I want is an SQL equivallent of "\ds" command, which will return
> a list of all sequences in the current database.  I need this for a
> periodically run script so that I could keep an eye on all sequences
> in the database.

\ds *is* the psql command for listing sequences:

arc=> create sequence testme;
CREATE
arc=> \ds
       List of relations
  Name  |   Type   |   Owner
--------+----------+-----------
 testme | sequence | arc_admin
(1 row)

-- Brett
                                          http://www.chapelperilous.net/
------------------------------------------------------------------------
Thrashing is just virtual crashing.

Re: SQL equivallent to "\ds" in psql

From

"Brett W. McCoy"

Date:

18 October 2001, 03:20:44

On 17 Oct 2001, Arcady Genkin wrote:

> Where does Postgres store information about the sequences?  I tried
> looking in the tables produced by "\dS", but could find no references
> to the sequences. :(

Oops, I thought you had made a typo, but I made a thinko.  Use the -E
option to generate the SQL to pull out sequences:

arc=> create sequence testme;
CREATE
arc=> \ds
********* QUERY *********
SELECT c.relname as "Name",
  (CASE WHEN relkind = 'S' THEN 'sequence'::text ELSE 'index'::text END)
as "Type",
  u.usename as "Owner"
FROM pg_class c, pg_user u
WHERE c.relowner = u.usesysid AND relkind in ('S')
  AND c.relname !~ '^pg_'
UNION
SELECT c.relname as "Name",
  (CASE WHEN relkind = 'S' THEN 'sequence'::text ELSE 'index'::text END)
as "Type",
  NULL as "Owner"
FROM pg_class c
WHERE not exists (select 1 from pg_user where usesysid = c.relowner) AND
relkind in ('S')
  AND c.relname !~ '^pg_'

ORDER BY "Name"
*************************

       List of relations
  Name  |   Type   |   Owner
--------+----------+-----------
 testme | sequence | arc_admin
(1 row)

-- Brett
                                          http://www.chapelperilous.net/
------------------------------------------------------------------------
Sometime when you least expect it, Love will tap you on the shoulder...
and ask you to move out of the way because it still isn't your turn.
        -- N.V. Plyter