How to get only the table names of a database in PostgreSQL

Question

I have a couple of bash scripts that query a mysql database and get the table names, I use the following command:

mysql -NBA --user=$DB_user --password=$DB_pass --database=$DB_name -e 'show tables'

With the -NBA parameters I filter the result and get something like this:

    categories
    colections
    colors
    files

In postgreSQL with psql I'm trying to achieve the same format, I'm reading the documentation and using the following parameters:

psql --username=$DB_user -W --host=$HOST --dbname=$DB_name -Atc '\dt'

An this is the best I can get

    public|categories|table|user
    public|colections|table|dbuser
    public|colors|table|dbuser
    public|files|table|dbuser

In the worst case I need to parse this to get only the names of the table, but if someone know the way to achieve what I want I be glad.

http://www.postgresql.org/docs/9.1/static/infoschema-tables.html — cwallenpoole, Apr 22 '15 at 21:49
You can use awk to get just the table names in your bash script... — ryekayo, Apr 22 '15 at 21:49

score 5 · Accepted Answer · edited May 23 '17 at 12:28

This SQL query gets the desired information (or any variants thereof):

SELECT table_name
FROM   information_schema.tables
WHERE  table_schema = 'public' -- mysql does not have schemas
ORDER  BY 1;

So from the shell:

psql --username=$DB_user -W --host=$HOST --dbname=$DB_name -Atc 'SELECT table_name FROM information_schema.tables WHERE table_schema = \'public\' ORDER BY 1'

You may want to use quote_ident(table_name) to get escaped table names:

;DELETE FROM users; -- becomes ";DELETE FROM users; --"

Note that you only get table names your current user has access to. About the information schema and system catalogs:

How to check if a table exists in a given schema

How to get only the table names of a database in PostgreSQL

1 Answers1