How to list database tables in PostgreSQL

Geometric design with "how to list tables in Postgres".

Luke Posey, Product Manager

Jan 2, 2024

Avatar for Luke Posey

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 with SQL.

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.

Screenshot of Quadratic showing a SQL query on the code panel with data output on the spreadsheet.

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.

Screenshot of Quadratic with SQL on the right and an AI Assistant who helped write the query, along with data on the spreadsheet resulting from the query.

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

Using the schema viewer in Quadratic is an even easier way to browse your tables. 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.

Screenshot of Quadratic with code on the right, data on the spreadsheet, and and arrow from the sheet to the schema viewer on the right under the code.

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 transform raw data into actionable insights quickly.

Quadratic logo

The spreadsheet with AI.

Use Quadratic for free
  • SpreadsheetSimple illustration of a spreadsheet application
  • +
  • CodeSimple illustration of a notebook application
  • +
  • ConnectionsSimple illustration of a spreadsheet dashboard