Thread: selecting tables and columns

Hi does postgresql have a similar command to SHOW in MySQL.
I want to select all tables that match a criteria then select all columns.
from those tables.

in MySQL:  "SHOW tables like 'stu%'";
                   "SHOW columns from ".$tbl." ";
using php variable

thanks
Bob

On Mon, 2004-06-07 at 18:36, Robert Morgan wrote:
> Hi does postgresql have a similar command to SHOW in MySQL.
> I want to select all tables that match a criteria then select all columns.
> from those tables.
>
> in MySQL:  "SHOW tables like 'stu%'";
>                    "SHOW columns from ".$tbl." ";
> using php variable

The problem here is that in PostgreSQL the presentation layer is complex
enough that it was incorporated into the psql monitor program
originally, via a series of commands implemented with back slash, like
\d to show all tables, and \d tablename to show info on table tablename.

Now, as of 7.4 or so, PostgreSQL implements the INFORMATION_SCHEMA as
defined by the SQL Spec.

A good way to explore the information schema is to do the following from
a psql session:

psql mydatabase
mydatabase=> set search_path TO INFORMATION_SCHEMA, public;
mydatabase=> \d
                             List of relations
       Schema       |              Name               |   Type   |
Owner
--------------------+---------------------------------+----------+----------
 information_schema | applicable_roles                | view     |
postgres
 information_schema | check_constraints               | view     |
postgres
 information_schema | column_domain_usage             | view     |
postgres
 information_schema | column_privileges               | view     |
postgres
 information_schema | column_udt_usage                | view     |
postgres

and so on from there.  You can select * from information_schema.name
with any of the tables shown here to get information from the
information_schema.  The good news is that as other databases implement
the information_schema part of the SQL spec, this will be more and more
portable.

Bob,
Use "\d"...

Take a look at the help using the \? from the postgres prompt...

    :
    :
  Informational
    \d [NAME]      describe table, index, sequence, or view
    \d{t|i|s|v|S}  [PATTERN] (add "+" for more detail)
                   list tables/indexes/sequences/views/system tables
    :
    :

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Robert Morgan
Sent: Monday, June 07, 2004 8:37 PM
To: postgres
Subject: [NOVICE] selecting tables and columns


Hi does postgresql have a similar command to SHOW in MySQL.
I want to select all tables that match a criteria then select all columns.
from those tables.

in MySQL:  "SHOW tables like 'stu%'";
                   "SHOW columns from ".$tbl." ";
using php variable

thanks
Bob


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match