Thread: Get the tables names?

Get the tables names?

From
"Magnus Landahl"
Date:
Hi everybody!

Is it possible to get the names of all tables in the database with a sql
query??

Best regards,

Magnus




Re: Get the tables names?

From
Roberto Mello
Date:
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 !!! ***


Re: Get the tables names?

From
Dado Feigenblatt
Date:
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.






Re: Get the tables names?

From
Joel Burton
Date:
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



Re: Get the tables names?

From
Tom Lane
Date:
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


Re: Get the tables names?

From
darcy@druid.net (D'Arcy J.M. Cain)
Date:
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.


Re: Get the tables names?

From
"Magnus Landahl"
Date:
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




Re: Get the tables names?

From
"dado feigenblatt"
Date:
----- 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.






Re: Re: Get the tables names?

From
Stephan Szabo
Date:
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