How to view table relation in PostgreSQL Server?

Question

How to view table relation in PostgreSQL server? I have tried several times and more than 2 years ago to find table relation in PostgreSQL server but couldn't get any help. So are there any way to find table relation same in SQL server or Access? Or is it possible to view table relation in PostgreSQL server?

@scaisEdge, How to remove that? Execute me, could you tell me a bit more deeper? Please please help...sir. Thank you very much, — SmallCorner, Jan 23 '17 at 10:53

score 9 · Answer 1 · edited Oct 12 '18 at 05:22

9

If you want to list all the relationships that start from a table, you must search the foreign keys. In Postgresql it means to search for constraints.

It is solved in this other question: SQL to list table foreign keys

From the answer:

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

edited Oct 12 '18 at 05:22

catch22

1,564
1
18
41

answered Jan 23 '17 at 11:00

Daniele Licitra

1,520
21
45

Thank you very much for your information, can I ask a bit? According to this line " tc.table_name='mytable';" , then the 'mytable' is the master table that we need to find foreign key key right? So ex: if my master table is M_CUSTOMER, then should I put it there? Thank you very much, – SmallCorner Jan 23 '17 at 11:19
if you have parent-sons relationship, you must put the son table name: postgres check the existence of parent record when you insert the son. For example, document and row: you must use "row" table, postgresql check if row.doc_id exists in document, otherwise an exception is thrown (you are trying to insert a record with doc_id = 3 but no record with id=3 is found in doc table ) – Daniele Licitra Jan 23 '17 at 11:25

score 1 · Answer 2 · answered Jan 23 '17 at 10:52

1

You could try using a UI like DataGrip or pgAdmin. I use DataGrip on my Postgres apps. You may simply try using the postgres interactive shell -- psql.

Best of luck

answered Jan 23 '17 at 10:52

user7457147

21
1

Thank you very much for your suggestion. I will try then will let you know, hopefully that it will probably fix. Thanks sir. – SmallCorner Jan 23 '17 at 10:56

score 0 · Answer 3 · answered Nov 28 '22 at 21:59

I know this question is not exactly about that, but I think lots of people land here looking for an easy way to see the relationships between tables using different data types (like parent id with bigint and child foreign key int, and vice versa).

Here's a quick way to spot all the columns with different types between parents and child tables:

SELECT
tc.constraint_name, tc.table_name, kcu.column_name, 
ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name,
cf.data_type AS child_data_type, 
cp.data_type AS parent_data_type
FROM 
information_schema.table_constraints AS tc 
JOIN information_schema.key_column_usage AS kcu
  ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
  ON ccu.constraint_name = tc.constraint_name
JOIN information_schema.tables as t
    on t.table_name = tc.table_name and t.table_catalog  = tc.table_catalog and t.table_schema = tc.table_schema 
JOIN information_schema.columns as cf
    on cf.table_name = tc.table_name and cf.column_name = kcu.column_name and cf.table_catalog = tc.table_catalog and cf.table_schema = tc.table_schema 
JOIN information_schema.columns as cp
    on cp.table_name = ccu.table_name and cp.column_name = ccu.column_name and cp.table_catalog = ccu.table_catalog and cp.table_schema = ccu.table_schema 
WHERE constraint_type = 'FOREIGN KEY'
and cf.data_type <> cp.data_type;

Enjoy!

How to view table relation in PostgreSQL Server?

3 Answers3