Find related columns in between two Table

Question

Imagine, you have two tables. And each Table has a lot of columns.

I did not create the database, so I really don't know what columns are related. One problem is there's no same Column name in between two tables, So I can not figure out by column name.

How can I find the column which is related to two different tables?

Answer 1

If there are foreign keys between the tables, then you can find the relationship between them. To do this, you can call \d on a table and see the foreign keys associated with its columns. Without explicit foreign keys, then there is no way to determine the relationship between tables except by talking with the developers and/or technical decision-makers.

An example of how the foreign key relationship is displayed in psql:

postgres=# alter table pgbench_accounts add constraint "pgb_accounts_branches_fk" foreign key (bid) references pgbench_branches (bid);
ALTER TABLE
postgres=# \d pgbench_accounts
              Table "public.pgbench_accounts"
  Column  |     Type      | Collation | Nullable | Default 
----------+---------------+-----------+----------+---------
 aid      | integer       |           | not null | 
 bid      | integer       |           |          | 
 abalance | integer       |           |          | 
 filler   | character(84) |           |          | 
Indexes:
    "pgbench_accounts_pkey" PRIMARY KEY, btree (aid)
Foreign-key constraints:
    "pgb_accounts_branches_fk" FOREIGN KEY (bid) REFERENCES pgbench_branches(bid)

postgres=# \d pgbench_branches
              Table "public.pgbench_branches"
  Column  |     Type      | Collation | Nullable | Default 
----------+---------------+-----------+----------+---------
 bid      | integer       |           | not null | 
 bbalance | integer       |           |          | 
 filler   | character(88) |           |          | 
Indexes:
    "pgbench_branches_pkey" PRIMARY KEY, btree (bid)
Referenced by:
    TABLE "pgbench_accounts" CONSTRAINT "pgb_accounts_branches_fk" FOREIGN KEY (bid) REFERENCES pgbench_branches(bid)

postgres=#