Database

_Flaviu wrote:

As far as I understood, "use ..." is not valid command in Postgre SQL

\connect

SELECT TABLE_NAME, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES T   WHERE T.TABLE_CATALOG=$dbname AND T.TABLE_SCHEMA='public';

<pre lang="SQL">

SELECT *
FROM pg_catalog.pg_tables
WHERE schemaname != 'pg_catalog' AND 
    schemaname != 'information_schema';

Quote:

Summary: in this tutorial, you will learn how to show tables in PostgreSQL using psql tool and pg_catalog schema.

$ psql -E -l
********* QUERY **********
SELECT d.datname as "Name",
       pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
       d.datcollate as "Collate",
       d.datctype as "Ctype",
       pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
<hr />

                                  List of databases
   Name    |  Owner   | Encoding  |   Collate   |    Ctype    |   Access privileges   
-----------+----------+-----------+-------------+-------------+-----------------------
 template0 | postgres | UTF8      | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres          +
           |          |           |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8      | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres          +
           |          |           |             |             | postgres=CTc/postgres
 test      | ebacon   | UTF8      | en_CA.UTF-8 | en_CA.UTF-8 | 
(3 rows)

\dt<dt> command inside psql.  Then all you need to do is to figure out how to combine both queries into one.<br />

<div class="signature">Keep Calm and Carry On</div></dt>

psql -E test -c "\dt". You can then take that output and combine with the previous one to get a listing of all tables across all databases.<br />

<div class="signature">Keep Calm and Carry On</div>

SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';

USE database my_database

ERROR: syntax error at or near "USE";
Error while executing the query

SELECT * FROM information_schema.tables;

SELECT * FROM information_schema.tables WHERE table_schema = 'schema_name';

\dt *.*

\dt schema_name.*