pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Get 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