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.

You can use awk to get just the table names in your bash script... — ryekayo
– ryekayo, Commented Apr 22, 2015 at 21:49

Community · Accepted Answer · 2017-05-23 12:28:17Z

5

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

edited May 23, 2017 at 12:28

CommunityBot

11 silver badge

answered Apr 22, 2015 at 21:59

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related