Re: really novice with Postgres !

Hi Jean,

First, as a novice you will soon find someone telling you that the
convention on the PostgreSQL mailing list is to "bottom post", i.e. post
your reply below the message you are replying to so people can read the
full history top to bottom.

On 08/02/2013 06:31 AM, Jean MAURICE wrote:
> Hi Tovo,
>
> I saw there is a view 'tables' that gives the list of the tables. How
> do we use a view in Postgres ?
> I tried
> SELECT * FROM tables;
> but the error is something like 'the relation 'tables' doesn't exist'
> (my Postgres is in French !)
>
Initial learning curves are always steep. In PostgreSQL tables are
organized into sets called "schemas." In a default installation the
tables will typically be in a schema called "public." It's easy to be
unaware that this is happening because, also by default, the "public"
schema is in your search path. You can check your search path with the
"show search_path;" statement.

Another schema is the "pg_catalog" schema which is visible by default
and contains lots of tables/pseudo-tables/views that underpin the inner
workings of PostgreSQL but which can be useful for the types of queries
you seek.

The information_schema is not in your search path by default but also
contains tables/views that can help you (and which are generally easier
to use than the pg_catalog tables). To access a table either explicitly
(due to the same table-name existing in multiple schemas in the search
path) or because the schema is not in the search path, simply prepend
the schema to the table, i.e. "select * from information_schema.columns
where table_name='foo' and table_schema='public';".
I have some more questions :
>>> Is there a SQL command to
>>>   - test if a database exists
Look in pg_catalog.pg_database
>>>   - test if a table exists
information_schema.tables
>>>   - get the list of the tables of a database
information_schema.tables again
>>>   - get the structure (list of fields) of a table
information_schema.columns

One trick is to run the "psql" terminal with the "-E" option. This will
cause it to show the actual commands it sends to the server in response
to the various backslash commands. For example if you want to see a list
of table (\dt) it would show the command it sent to the server:

********* QUERY **********
SELECT n.nspname as "Schema",
   c.relname as "Name",
   CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f'
THEN 'foreign table' END as "Type",
   pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
       AND n.nspname <> 'pg_catalog'
       AND n.nspname <> 'information_schema'
       AND n.nspname !~ '^pg_toast'
   AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

You can use that query as a jumping off point to learn where the data is
located or to modify the query to suit your use.

Cheers,
Steve