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!
Comments
