How to list database tables in PostgreSQL

Exploring the tables in your Postgres database is a common task. Especially as your database grows, it becomes increasingly difficult to view and keep track of all the tables and their data. In this post, we'll show you how to list database tables in Postgres and easily visualize them in a spreadsheet.

Query to list tables in Postgres

Below is a simple SQL query listing all the tables in your PostgreSQL database.

SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'public';

The query returns the full list of user generated tables in the database.

Query to view a table's schema

You can query Postgres to list columns and data types (schema) in an individual table by running the following query:

SELECT column_name, data_type 
FROM information_schema.columns 
WHERE table_name = 'your_table_name';

The query returns the schema (column names and data types) for your table of choice.

Advanced queries for exploring lists of tables and their schema

The following query lists all the columns in a table and shows their data type, whether or not they are nullable, default values, and any associated descriptions.

SELECT 
 c.column_name,
 c.data_type,
 c.character_maximum_length,
 c.is_nullable,
 c.column_default,
 pgd.description
FROM information_schema.columns c
LEFT JOIN pg_catalog.pg_description pgd ON 
 pgd.objoid = (SELECT oid FROM pg_catalog.pg_class WHERE relname = c.table_name)
    AND pgd.objsubid = c.ordinal_position
WHERE c.table_name = 'your_table_name'
ORDER BY c.ordinal_position;

The following query lets you view a table's primary keys, foreign keys, and indexes.

SELECT
 tc.constraint_name,
 tc.constraint_type,
 kcu.column_name,
 ccu.table_name AS foreign_table_name,
 ccu.column_name AS foreign_column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
LEFT JOIN information_schema.constraint_column_usage ccu
    ON ccu.constraint_name = tc.constraint_name
WHERE tc.table_name = 'your_table_name';

This final query lists the sizes for all tables in your database. It allows you to identify which tables are taking up the most storage, which are relevant to optimize, and other considerations for database administration.

SELECT
 relname as table_name,
 pg_size_pretty(pg_total_relation_size(relid)) as total_size,
 pg_size_pretty(pg_relation_size(relid)) as data_size,
 pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as external_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

Visualizing tables in your spreadsheets

An even easier way to browse your tables is to use the schema viewer in Quadratic. Skip the need to create a list of tables in Postgres with a query and browse the tables and their columns in a simple list view.

Once you're ready, you can begin querying your tables directly. By applying these queries, you can seamlessly list database tables in Postgres, analyze their schemas, and optimize your workflow. Using a tool like Quadratic’s schema viewer lets you browse tables at a glance, eliminate guesswork, and leverage AI-powered queries to quickly transform raw data into actionable insights.

You can start trying Quadratic today or contact us with any questions.