How to explore foreign key relationships in PostgreSQL

1. Inspect a table with psql

The quickest way to see the foreign keys of a single table is the \d meta-command in psql. It lists outgoing constraints ("Foreign-key constraints") and incoming ones ("Referenced by").

\d orders

2. Query the system catalog

To list every foreign key in a schema, query pg_constraint. This gives you the full relationship map: which table and column points to which.

SELECT
  conrelid::regclass  AS child_table,
  confrelid::regclass AS parent_table,
  pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE contype = 'f'
  AND connamespace = 'public'::regnamespace
ORDER BY child_table;

Find the records linked to one row

Knowing the schema is only half the job. To see the actual data, you still write one query per relationship, and one more each time you go a level deeper:

SELECT * FROM customers WHERE id = 42;
SELECT * FROM orders WHERE customer_id = 42;
SELECT * FROM order_items WHERE order_id IN (
  SELECT id FROM orders WHERE customer_id = 42
);

• Each level of depth means a new query or a new JOIN.
• Incoming relationships (who references this row?) are easy to forget.
• Results come back as flat tables, so the hierarchy lives in your head.

3. Browse relationships as a tree

DbTree reads the same foreign key metadata and turns it into a tree you can click through. Pick a table, optionally an ID, and expand: parent records, child records and their own relations load on demand. The selected record's fields appear on the right.

There is no SQL to write, and the connection is read-only, so you can explore a production database without risk of modifying it.

Which approach should you use?

• psql \d: you need the constraints of one table, right now.
• pg_constraint: you want a full map of the schema, for documentation or a script.
• DbTree: you are investigating data, following one record through several levels of relationships.

Frequently asked questions

How do I see which tables reference a table in PostgreSQL?

Run \d table_name in psql and read the "Referenced by" section, or query pg_constraint with confrelid = 'table_name'::regclass and contype = 'f'.

Does DbTree modify my PostgreSQL database?

No. DbTree connects in read-only mode and never writes to your database. Connection credentials stay on your machine.