Thread: Get the tables names?
Hi everybody! Is it possible to get the names of all tables in the database with a sql query?? Best regards, Magnus
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 !!! ***
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.
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
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
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.
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
----- 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.
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