PostgreSQL: list tables, schemas and columns

By Anil Gautam · Published and reviewed September 20, 2026

Run a query against information_schema.tables to list PostgreSQL tables in your SQL editor. Keep the schema name in the result. Otherwise, two tables with the same name can be easy to confuse.

List application tables

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

This shows tables and views visible to your current role in the connected database. For ordinary tables only, add AND table_type = 'BASE TABLE'. The result covers the connected database. Open another database connection if the table belongs elsewhere.

The visibility rules are documented in PostgreSQL's tables view reference. A missing row is not proof that an object was deleted: permissions can explain it.

Inspect columns before writing a query

Replace the illustrative public and orders names with a schema and table from your result.

SELECT ordinal_position, column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'orders'
ORDER BY ordinal_position;

Use this to distinguish a numeric identifier from text, check whether a field permits NULL, and locate the exact spelling of a timestamp column. For domains, arrays and user-defined types, inspect the additional fields in the columns reference; data_type alone is not a complete type definition.

Why does psql's backslash command fail?

\dt is a command interpreted by the psql terminal program. It is not SQL sent directly to PostgreSQL. Use it in psql; use the queries above in a GUI editor. A syntax error at the backslash does not mean your connection is broken.

Check the connection when tables seem missing

SELECT current_database(), current_user;
SHOW search_path;

Compare the database and role with the application configuration. Refer to a table as public.orders when you need to remove schema ambiguity. In plainDB, refresh the schema tree after a migration, then compare the tree with these query results. If both omit an expected object, investigate the connection and permissions before changing the UI or recreating the table.

Continue reading

Connect to PostgreSQL · Inspect indexes · Measure table size

For the desktop workflow, compare plainDB features on Mac and Windows or download the 14-day trial.