Finding Tables in PostgreSQL: Query Guide

To find tables in PostgreSQL, you query the system catalogs—specifically pg_tables, information_schema.tables, or pg_class—using a SELECT statement. You can list all tables in the current database, filter by schema, or search by name pattern. This article explains the main methods, how to choose between them, and common pitfalls.

Why You Need to Query System Catalogs

PostgreSQL does not have a single built-in command like SHOW TABLES (that’s MySQL). Instead, it stores metadata about every table, view, index, and sequence in system catalogs—ordinary tables that you can query with SQL. The most useful ones are:

• pg_tables – a view that lists tables and their properties.
• information_schema.tables – a standard SQL view that works across many database systems.
• pg_class – the underlying catalog table that stores all relations (tables, indexes, views, etc.).

Knowing these gives you full control to filter, sort, and join with other metadata, which is essential for database administration, scripting, and debugging.

Method 1: Using pg_tables (Simplest)

The pg_tables view is the easiest way to see user tables. It shows columns like schemaname, tablename, tableowner, and tablespace. To list all tables in the default public schema:

SELECT * FROM pg_tables WHERE schemaname = 'public';

If you want just the table names:

SELECT tablename FROM pg_tables WHERE schemaname = 'public' ORDER BY tablename;

To include tables from all schemas (excluding system schemas like pg_catalog and information_schema):

SELECT schemaname, tablename FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema') ORDER BY schemaname, tablename;

Method 2: Using information_schema.tables (Standard SQL)

The information_schema.tables view follows the SQL standard, so your queries are more portable across database systems (e.g., MySQL, SQL Server). It includes a table_type column that distinguishes ‘BASE TABLE’ from ‘VIEW’. To list all user tables:

SELECT table_schema, table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema NOT IN ('pg_catalog', 'information_schema') ORDER BY table_schema, table_name;

This is particularly useful if you’re writing scripts that might be reused with other databases.

Method 3: Using psql Meta-Commands

If you’re working in the psql command-line client, you don’t need to write SQL at all. The meta-command \dt lists all tables in the current schema:

\dt

To see tables in all schemas, use \dt *.*. To filter by name pattern, for example tables starting with “userâ€�:

\dt user*

This is the quickest way when you’re interactively exploring a database.

Filtering by Schema and Name Pattern

Often you need to find a table whose name you partially remember. Use LIKE or ILIKE (case-insensitive) on the table name:

SELECT tablename FROM pg_tables WHERE schemaname = 'public' AND tablename ILIKE '%order%';

If you’re looking in a specific schema (e.g., a schema called sales), replace public with sales. To see all schemas, query pg_namespace:

SELECT nspname FROM pg_namespace;

Common Pitfalls and Misconceptions

One common mistake is forgetting that pg_tables only shows tables, not views. If you need views, use pg_views or filter information_schema.views. Another pitfall is assuming that all tables are in the public schema—you might have multiple schemas, and tables can be hidden if you don’t specify the schema. Also, pg_class includes system tables and indexes, so if you query it directly, you must filter with relkind = 'r' (ordinary table) and exclude system schemas:

SELECT relname FROM pg_class WHERE relkind = 'r' AND relnamespace NOT IN ('pg_catalog'::regnamespace, 'information_schema'::regnamespace);

This is more advanced but gives you access to additional columns like reltuples (estimated row count).

Frequently Asked Questions

How do I list all tables in a specific schema?

Use SELECT tablename FROM pg_tables WHERE schemaname = 'your_schema'; or in psql, \dt your_schema.*. Replace your_schema with the actual schema name.

How do I find tables by name pattern?

Use LIKE or ILIKE in a WHERE clause. For example, SELECT tablename FROM pg_tables WHERE tablename ILIKE '%customer%'; finds all tables with “customerâ€� in the name, case-insensitively.

Why does \dt show no tables even though they exist?

This usually happens because you’re connected to the wrong database or schema. Check your current database with SELECT current_database(); and schema with SELECT current_schema();. Also, ensure the tables are not in a different schema that isn’t in your search_path.

Can I see table sizes or row counts with these queries?

Yes, but you need to join with pg_class and pg_namespace. For example, SELECT relname AS table, reltuples::bigint AS estimated_rows FROM pg_class WHERE relkind = 'r' AND relnamespace = 'public'::regnamespace; gives estimated rows. For exact counts, you’d need to run SELECT count(*) FROM table; which can be slow on large tables.

What is the difference between pg_tables and information_schema.tables?

pg_tables is PostgreSQL-specific and includes only tables (not views), with columns like tableowner. information_schema.tables is a standard SQL view that includes both tables and views, and is more portable. For most purposes, either works, but pg_tables is simpler for PostgreSQL-only tasks.

How do I find tables that have a specific column?

Query information_schema.columns or pg_attribute. For example: SELECT table_name FROM information_schema.columns WHERE column_name = 'user_id'; lists all tables that have a column named user_id.

Related

• Convert Play FLAC in iTunes: Full Guide
• Soda Foundation Charter: What It Means
• Convert Table to Text in Word: Step-by-Step Guide