Обсуждение: 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
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 >
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 >
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
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.
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