So I have no idea how to do this and I've been reading the documentation and searching around but I can't seem to find anything. \d gives me a nice list of all the tables and information on them but I want to be able to send a command to the postgresql database and pipe it into a text file that will contain just the table names.
4 Answers
Run psql with the following switch. It'll show the internal commands psql sends to the catalog to get this kind of information:
-E --echo-hiddenEcho the actual queries generated by \d and other backslash commands. You can use this to study psql's internal operations. This is equivalent to setting the variable ECHO_HIDDEN from within psql.
http://www.postgresql.org/docs/current/static/app-psql.html
e.g.:
~ $ psql -E
SET
psql (9.2.4)
Type "help" for help.
denis=# \dt
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
public | test | table | denis
(1 row)
answered Jun 18, 2013 at 15:06
Denis de Bernardy
79.1k14 gold badges138 silver badges158 bronze badges
Comments
Alternatively to Denis solution, you might want:
select table_name from information_schema.tables;
but this is bound to cause problems to lack of schema information.
2 Comments
Denis de Bernardy
Ah, yes. I always forget that thing exists. :-P
This is what I came up with to help print just the name's of tables
SELECT c.relname as "Name"
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
AND n.nspname <> 'pg_catalog'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1;
1 Comment
Denis de Bernardy
Safely remove the join on
pg_roles. If you want to see all tables, rather than only those your user sees, also remove the pg_table_is_visible() call.A quick way to view your table names
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
More info is available with
\dt
or
\dt+
