Thread
-
SQL equivallent to "\ds" in psql
Arcady Genkin <a.genkin@utoronto.ca> — 2001-10-17T20:35:26Z
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
Mitch Vincent <mvincent@cablespeed.com> — 2001-10-17T21:31:07Z
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
Jason Earl <jdearl@yahoo.com> — 2001-10-17T22:05:14Z
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
Brett W. McCoy <bmccoy@chapelperilous.net> — 2001-10-18T04:00:44Z
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
Brett W. McCoy <bmccoy@chapelperilous.net> — 2001-10-18T04:03:44Z
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