Database metadata queries (WAS Re: Moving from MySQL to PGSQL....some questions) - Mailing list pgsql-general

Shachar,

This is a very helpful tidbit that I hadn't realized and it will save me a
significant amount of time figuring out such queries in the coming weeks.
Thank you.

Would it be worthwhile to move many of these \d queries into the system
schema, as views on various system tables? I've thought that it would be
very useful to be able to access these things through the web or other
clients.  I could see the benefit of providing users with a consistent
interface
to such "database metadata", no matter what client one is using. (OTOH, one
could argue, learning to do that is a pgsql rite-of-passage. ;-> ).

Shawn Harrison

----- Original Message -----
> If you run psql with the "-E" parameter, whenever you execute a psql
> command that translates to a query, that query will be displayed on
> screen. This allows you to check out what queries you need for certain
> operations.
>
> For example - to check all the tables in the current database/schema:
> $ psql -E db
> Welcome to psql 7.4.1, the PostgreSQL interactive terminal.
>
> Type:  \copyright for distribution terms
>        \h for help with SQL commands
>        \? for help on internal slash commands
>        \g or terminate with semicolon to execute query
>        \q to quit
>
> db=# \dt
> ********* 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' END as
"Type",
>   u.usename as "Owner"
> FROM pg_catalog.pg_class c
>      LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
>      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
> WHERE c.relkind IN ('r','')
>       AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
>       AND pg_catalog.pg_table_is_visible(c.oid)
> ORDER BY 1,2;
> **************************
>
>            List of relations
>  Schema |     Name     | Type  | Owner
> --------+--------------+-------+-------
>
> Check out the rest of the \d* commands for more listings (\? will give
> you the list).
>
> Also, it pays to look up the meaning of the above in the documentation.
> The system tables are documented in
> http://www.postgresql.org/docs/7.4/static/catalogs.html
>
> >> Any help would be appreciated.
> >
>        Shachar
>
> --
> Shachar Shemesh
> Lingnu Open Systems Consulting
> http://www.lingnu.com/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html