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?
- removed the improper tagScaisEdge– ScaisEdge2017-01-23 10:46:21 +00:00Commented Jan 23, 2017 at 10:46
- @scaisEdge, How to remove that? Execute me, could you tell me a bit more deeper? Please please help...sir. Thank you very much,SmallCorner– SmallCorner2017-01-23 10:53:14 +00:00Commented Jan 23, 2017 at 10:53
- i have already done .. my comment was for your info onlyScaisEdge– ScaisEdge2017-01-23 10:54:28 +00:00Commented Jan 23, 2017 at 10:54
Add a comment |
3 Answers
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'; 
2 Comments
SmallCorner
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,
Daniele Licitra
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 )
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
1 Comment
SmallCorner
Thank you very much for your suggestion. I will try then will let you know, hopefully that it will probably fix. Thanks sir.
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!
