CODEWITHSUNDEEP
mysqlbashpostgresqlpsql
Alevsk
Alevsk

Reputation: 37

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

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.

Upvotes: 2

Views: 3426

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656616

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:

Upvotes: 5

Related Questions