"Show tables like" in postgresql

Question

Is there any way to list all tables that contain certain keywords in psql?

In other words, is there a similar command as in mysql:

show tables like "%test%" ?

Soren

Answer 1

Just found the answer..

\dt *test* will pull tables: 123_test_234, test_234, 123_test etc.

\dt *test will pull tables: 123_test etc.

\dt test* will pull tables: test_234 etc.

Not sure if there is a similar question. If there is, maybe I should delete this.

Soren

I had to double quote the text like \dt *"test"* or \dt "test"* to fetch the matching tables. – Hold

Answer 2

The answer offered by @peipei uses the meta-command \dt, but if you want to filter out the columns except the table name (as SHOW TABLES do in mysql) you can do the following:

SELECT tablename
FROM pg_catalog.pg_tables 
WHERE 
   schemaname != 'pg_catalog'
AND 
   schemaname != 'information_schema'
AND
   tablename like '%test%';

Probably excluding the pg_catalog and information_schema is what you want to do in order to get the most common schemas where the non-system tables are created.

View tip: if you use that query a lot you can create a VIEW that behaves like a table of tables:

CREATE VIEW tables AS SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';

and then you can query it and remind it easily:

SELECT * FROM tables WHERE tablename LIKE '%test%';

Tip 2: Foreign tables won't appear in your search if you have them. You would need to query then in this other mode:

SELECT foreign_table_name 
FROM information_schema.foreign_tables
WHERE foreign_table_name LIKE '%test%';

Stag