5

How can I get the table names without a header or footer or anything else from a specified Postgres database with psql? That is, if I have tables called "table1" and "table2" then the output will be:

table1
table2

and only that. I've been trying various combinations of things, but it seems like it should be a lot easier than I'm making it.

migrated from serverfault.com Mar 21 '15 at 11:22

This question came from our site for system and network administrators.

7

In PostgreSQL (as described in documentation, The Information Schema):

SELECT table_name
FROM information_schema.tables
WHERE table_type='BASE TABLE'
AND table_schema='public';

For MySQL you would need table_schema='dbName' and for MSSQL remove that condition.

Notice that "only those tables and views are shown that the current user has access to". Also, if you have access to many databases and want to limit the result to a certain database, you can achieve that by adding condition AND table_catalog='yourDatabase' (in PostgreSQL).

If you'd also like to get rid of the header showing row names and footer showing row count, you could either start the psql with command line option -t (short for --tuples-only) or you can toggle the setting in psql's command line by \t (short for \pset tuples_only). This could be useful for example when piping output to another command with \g [ |command ].

  • Hi Esa, that doesn't do the job. It prints a header saying "table name" and a dashed line and underneath it prints "(2 rows)". I just want the table names. – seumasmac Mar 21 '15 at 8:11
  • 2
    This is still the solution. It's just how psql shows the results. If you want to suppress header and footer, you can command \pset tuples_only first. – Esa Jokinen Mar 21 '15 at 8:19
  • How it shows the results was exactly my point! That's why I said "and only that" and gave an example of the output. I'm not sure how I could have made my question any clearer. But thanks for your help. If you add in that you need to use 'psql -q' and then run \pset tuples_only before the main query then I'll accept your answer (that was how I figured out to suppress all extra output) – seumasmac Mar 21 '15 at 9:19
  • 1
    Now the answer should have everything that was asked and desired. – Esa Jokinen Mar 21 '15 at 10:29
  • 2
    an easier way is \dt – John Bachir Dec 8 '16 at 18:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.