pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGet the tables names?
10+ messages / 9 participants
[nested] [flat]
* Get the tables names?
@ 2001-07-19 09:04 Magnus Landahl <magnus.landahl@datessa.se>
0 siblings, 3 replies; 10+ messages in thread
From: Magnus Landahl @ 2001-07-19 09:04 UTC (permalink / raw)
To: pgsql-sql
Hi everybody!
Is it possible to get the names of all tables in the database with a sql
query??
Best regards,
Magnus
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Get the tables names?
@ 2001-07-19 22:51 Nils Zonneveld <nils@mbit.nl>
parent: Magnus Landahl <magnus.landahl@datessa.se>
2 siblings, 0 replies; 10+ messages in thread
From: Nils Zonneveld @ 2001-07-19 22:51 UTC (permalink / raw)
To: pgsql-sql
Magnus Landahl wrote:
>
> Hi everybody!
>
> Is it possible to get the names of all tables in the database with a sql
> query??
>
select tablename from pg_tables where tablename !~ '^pg_';
or more SQL92 like:
select tablename from pg_tables where tablename not like 'pg_%';
Kind regards,
Nils Zonneveld
--
Alles van waarde is weerloos
Lucebert
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Get the tables names?
@ 2001-07-20 18:27 Roberto Mello <rmello@cc.usu.edu>
parent: Magnus Landahl <magnus.landahl@datessa.se>
2 siblings, 1 reply; 10+ messages in thread
From: Roberto Mello @ 2001-07-20 18:27 UTC (permalink / raw)
To: Magnus Landahl <magnus.landahl@datessa.se>; +Cc: pgsql-sql
On Thu, Jul 19, 2001 at 11:04:40AM +0200, Magnus Landahl wrote:
> Hi everybody!
>
> Is it possible to get the names of all tables in the database with a sql
> query??
SELECT tablename FROM pg_tables WHERE tablename NOT LIKE '%pg_%';
The "NOT LIKE" part is to avoid getting the PostgreSQL internal tables
in the output.
-Roberto
--
+----| http://fslc.usu.edu USU Free Software & GNU/Linux Club |------+
Roberto Mello - Computer Science, USU - http://www.brasileiro.net
http://www.sdl.usu.edu - Space Dynamics Lab, Developer
*** TURN OFF YOUR SYSTEM NOW !!! ***
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Get the tables names?
@ 2001-07-20 19:58 Dado Feigenblatt <dado@wildbrain.com>
parent: Magnus Landahl <magnus.landahl@datessa.se>
2 siblings, 2 replies; 10+ messages in thread
From: Dado Feigenblatt @ 2001-07-20 19:58 UTC (permalink / raw)
To: Magnus Landahl <magnus.landahl@datessa.se>; pgsql-sql
Magnus Landahl wrote:
>Hi everybody!
>
>Is it possible to get the names of all tables in the database with a sql
>query??
>
>Best regards,
>
>Magnus
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo@postgresql.org so that your
>message can get through to the mailing list cleanly
>
>
>
Not sure if this is the best way, but it works.
SELECT relname , relowner from pg_class where relkind = 'r';
The only thing is that this includes system tables.
So if you want to strip those you need to
SELECT relname , relowner FROM pg_class WHERE relkind = 'r' and
relowner != 26;
Is user postgres always 26? Maybe you have to find that out first.
--
Dado Feigenblatt Wild Brain, Inc.
Technical Director (415) 553-8000 x???
dado@wildbrain.com San Francisco, CA.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Get the tables names?
@ 2001-07-20 20:43 Joel Burton <jburton@scw.org>
parent: Dado Feigenblatt <dado@wildbrain.com>
1 sibling, 2 replies; 10+ messages in thread
From: Joel Burton @ 2001-07-20 20:43 UTC (permalink / raw)
To: Dado Feigenblatt <dado@wildbrain.com>; +Cc: Magnus Landahl <magnus.landahl@datessa.se>; pgsql-sql
On Fri, 20 Jul 2001, Dado Feigenblatt wrote:
> Magnus Landahl wrote:
>
> >Hi everybody!
> >
> >Is it possible to get the names of all tables in the database with a sql
> >query??
> >
> >Best regards,
> >
> >Magnus
> >
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 3: if posting/reading through Usenet, please send an appropriate
> >subscribe-nomail command to majordomo@postgresql.org so that your
> >message can get through to the mailing list cleanly
> >
> >
> >
> Not sure if this is the best way, but it works.
> SELECT relname , relowner from pg_class where relkind = 'r';
>
> The only thing is that this includes system tables.
> So if you want to strip those you need to
> SELECT relname , relowner FROM pg_class WHERE relkind = 'r' and
> relowner != 26;
>
> Is user postgres always 26? Maybe you have to find that out first.
system tables all ~ '^pg', which is probably a better check than
user=postgresql.
hth,
--
Joel Burton <jburton@scw.org>
Director of Information Systems, Support Center of Washington
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Get the tables names?
@ 2001-07-20 21:02 Tom Lane <tgl@sss.pgh.pa.us>
parent: Dado Feigenblatt <dado@wildbrain.com>
1 sibling, 0 replies; 10+ messages in thread
From: Tom Lane @ 2001-07-20 21:02 UTC (permalink / raw)
To: Dado Feigenblatt <dado@wildbrain.com>; +Cc: Magnus Landahl <magnus.landahl@datessa.se>; pgsql-sql
Dado Feigenblatt <dado@wildbrain.com> writes:
> The only thing is that this includes system tables.
> So if you want to strip those you need to
> SELECT relname , relowner FROM pg_class WHERE relkind = 'r' and
> relowner != 26;
> Is user postgres always 26?
It certainly is not. Even if it was, the above would exclude ordinary
tables that had been created by the dbadmin/superuser.
The convention that's really used is that system tables have names
starting with 'pg_' --- the code will actually not let you create a
table with such a name, so that the convention can be relied on.
So the correct way to exclude system tables is
SELECT * FROM pg_class WHERE relkind = 'r' and relname not like 'pg_%';
If you try "\d" in psql after starting it with -E option, you will
discover that this is indeed what psql does ...
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Get the tables names?
@ 2001-07-20 21:14 Magnus Landahl <magnus.landahl@datessa.se>
parent: Joel Burton <jburton@scw.org>
1 sibling, 0 replies; 10+ messages in thread
From: Magnus Landahl @ 2001-07-20 21:14 UTC (permalink / raw)
To: pgsql-sql
Hi Everybody!!
Well I tried this (below) and it workt perfectly. Thanks a lot for ALL
answers to my question!
>select tablename from pg_tables where tablename not like 'pg_%';
Kind regards,
Magnus Landahl
"Joel Burton" <jburton@scw.org> skrev i meddelandet
news:Pine.LNX.4.21.0107201642270.3206-100000@olympus.scw.org...
> On Fri, 20 Jul 2001, Dado Feigenblatt wrote:
>
> > Magnus Landahl wrote:
> >
> > >Hi everybody!
> > >
> > >Is it possible to get the names of all tables in the database with a
sql
> > >query??
> > >
> > >Best regards,
> > >
> > >Magnus
> > >
> > >
> > >
> > >---------------------------(end of
broadcast)---------------------------
> > >TIP 3: if posting/reading through Usenet, please send an appropriate
> > >subscribe-nomail command to majordomo@postgresql.org so that your
> > >message can get through to the mailing list cleanly
> > >
> > >
> > >
> > Not sure if this is the best way, but it works.
> > SELECT relname , relowner from pg_class where relkind = 'r';
> >
> > The only thing is that this includes system tables.
> > So if you want to strip those you need to
> > SELECT relname , relowner FROM pg_class WHERE relkind = 'r' and
> > relowner != 26;
> >
> > Is user postgres always 26? Maybe you have to find that out first.
>
> system tables all ~ '^pg', which is probably a better check than
> user=postgresql.
>
>
> hth,
> --
> Joel Burton <jburton@scw.org>
> Director of Information Systems, Support Center of Washington
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Get the tables names?
@ 2001-07-22 12:12 D'Arcy J.M. Cain <darcy@druid.net>
parent: Roberto Mello <rmello@cc.usu.edu>
0 siblings, 0 replies; 10+ messages in thread
From: darcy@druid.net @ 2001-07-22 12:12 UTC (permalink / raw)
To: Roberto Mello <rmello@cc.usu.edu>; +Cc: Magnus Landahl <magnus.landahl@datessa.se>; pgsql-sql
Thus spake Roberto Mello
> On Thu, Jul 19, 2001 at 11:04:40AM +0200, Magnus Landahl wrote:
> > Hi everybody!
> >
> > Is it possible to get the names of all tables in the database with a sql
> > query??
>
> SELECT tablename FROM pg_tables WHERE tablename NOT LIKE '%pg_%';
>
> The "NOT LIKE" part is to avoid getting the PostgreSQL internal tables
> in the output.
Further, to only get tables and not views, indexes, sequences, etc you
can do this.
SELECT tablename FROM pg_tables
WHERE relkind = 'r' AND tablename NOT LIKE '%pg_%';
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Get the tables names?
@ 2001-07-23 17:10 dado feigenblatt <dado@wildbrain.com>
parent: Joel Burton <jburton@scw.org>
1 sibling, 1 reply; 10+ messages in thread
From: dado feigenblatt @ 2001-07-23 17:10 UTC (permalink / raw)
To: jburton@scw.org; +Cc: pgsql-sql
----- Original Message -----
From: "Joel Burton" <jburton@scw.org>
To: "Dado Feigenblatt" <dado@wildbrain.com>
Cc: "Magnus Landahl" <magnus.landahl@datessa.se>; "pgsql"
<pgsql-sql@postgresql.org>
Sent: Friday, July 20, 2001 1:43 PM
Subject: Re: Get the tables names?
> On Fri, 20 Jul 2001, Dado Feigenblatt wrote:
>
> > Magnus Landahl wrote:
> >
> > >Hi everybody!
> > >
> > >Is it possible to get the names of all tables in the database with a
sql
> > >query??
> > >
> > >Best regards,
> > >
> > >Magnus
> > >
> > >
> > >
> > >---------------------------(end of
broadcast)---------------------------
> > >TIP 3: if posting/reading through Usenet, please send an appropriate
> > >subscribe-nomail command to majordomo@postgresql.org so that your
> > >message can get through to the mailing list cleanly
> > >
> > >
> > >
> > Not sure if this is the best way, but it works.
> > SELECT relname , relowner from pg_class where relkind = 'r';
> >
> > The only thing is that this includes system tables.
> > So if you want to strip those you need to
> > SELECT relname , relowner FROM pg_class WHERE relkind = 'r' and
> > relowner != 26;
> >
> > Is user postgres always 26? Maybe you have to find that out first.
>
> system tables all ~ '^pg', which is probably a better check than
> user=postgresql.
>
>
> hth,
> --
> Joel Burton <jburton@scw.org>
> Director of Information Systems, Support Center of Washington
You never know when someone will name their tables starting with "pg".
Well, you never know when someone will create their tables as user postgres
either.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Re: Get the tables names?
@ 2001-07-23 21:56 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: dado feigenblatt <dado@wildbrain.com>
0 siblings, 0 replies; 10+ messages in thread
From: Stephan Szabo @ 2001-07-23 21:56 UTC (permalink / raw)
To: dado feigenblatt <dado@wildbrain.com>; +Cc: jburton@scw.org; pgsql-sql
On Mon, 23 Jul 2001, dado feigenblatt wrote:
> > system tables all ~ '^pg', which is probably a better check than
> > user=postgresql.
>
> You never know when someone will name their tables starting with "pg".
> Well, you never know when someone will create their tables as user postgres
> either.
You probably want to check for '^pg_' since you shouldn't be able to
create user table names starting with pg_. As the administrator on my
home test system:
sszabo=> create table "pg_a"(A int);
ERROR: Illegal class name 'pg_a'
The 'pg_' name prefix is reserved for system catalogs
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2001-07-23 21:56 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-07-19 09:04 Get the tables names? Magnus Landahl <magnus.landahl@datessa.se>
2001-07-19 22:51 ` Nils Zonneveld <nils@mbit.nl>
2001-07-20 18:27 ` Roberto Mello <rmello@cc.usu.edu>
2001-07-22 12:12 ` D'Arcy J.M. Cain <darcy@druid.net>
2001-07-20 19:58 ` Dado Feigenblatt <dado@wildbrain.com>
2001-07-20 20:43 ` Joel Burton <jburton@scw.org>
2001-07-20 21:14 ` Magnus Landahl <magnus.landahl@datessa.se>
2001-07-23 17:10 ` dado feigenblatt <dado@wildbrain.com>
2001-07-23 21:56 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-07-20 21:02 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox