CODEWITHSUNDEEP
mysqlsqlpostgresql
Raqasyinov
Raqasyinov

Reputation: 63

How to list all columns with it's reference in postgresql like in mysql?

So, i've been search through the internet and cant seems to find the solution for this...

Im trying to list all column with it's reference in postgresql. In mysql, the query would be like:

select table_schema, table_name, column_name, referenced_table_schema, referenced_table_name, referenced_column_name from informatioN_schema.key_column_usage where table_schema = '(Table Schema)';

And the result is:

setandlog    | access      | kode_privilege  | NULL                    | NULL                  | NULL
setandlog    | access      | kode_feature    | NULL                    | NULL                  | NULL
setandlog    | access      | kode_app        | NULL                    | NULL                  | NULL
setandlog    | access      | kode_credential | NULL                    | NULL                  | NULL
setandlog    | access      | username        | NULL                    | NULL                  | NULL
setandlog    | access      | kode_credential | setandlog               | cred_access           | kode_credential
setandlog    | access      | kode_privilege  | setandlog               | cred_access           | kode_privilege
setandlog    | access      | kode_feature    | setandlog               | cred_access           | kode_feature
setandlog    | access      | kode_app        | setandlog               | cred_access           | kode_app
setandlog    | access      | username        | setandlog               | login                 | username

but when im trying it in postgresql using this query:

select r.table_schema as table_schema, r.table_name as table_name, r.column_name as column_name, u.table_schema as referenced_table_schema, u.table_name as referenced_table_name, u.column_name as referenced_column_name
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE as u
inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as FK
    on U.CONSTRAINT_CATALOG = FK.UNIQUE_CONSTRAINT_CATALOG
    and U.CONSTRAINT_SCHEMA = FK.UNIQUE_CONSTRAINT_SCHEMA
    and U.CONSTRAINT_NAME = FK.UNIQUE_CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as R
    ON R.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
    AND R.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
    AND R.CONSTRAINT_NAME = FK.CONSTRAINT_NAME

The result is not as i expected like in the MySQL Query... Here's the result:

setandlog    | access            | kode_credential          | setandlog               | cred_access           | kode_credential
setandlog    | access            | kode_privilege           | setandlog               | cred_access           | kode_credential
setandlog    | access            | kode_feature             | setandlog               | cred_access           | kode_credential
setandlog    | access            | kode_app                 | setandlog               | cred_access           | kode_credential
setandlog    | access            | kode_credential          | setandlog               | cred_access           | kode_privilege
setandlog    | access            | kode_privilege           | setandlog               | cred_access           | kode_privilege
setandlog    | access            | kode_feature             | setandlog               | cred_access           | kode_privilege
setandlog    | access            | kode_app                 | setandlog               | cred_access           | kode_privilege
setandlog    | access            | kode_credential          | setandlog               | cred_access           | kode_feature
setandlog    | access            | kode_privilege           | setandlog               | cred_access           | kode_feature
setandlog    | access            | kode_feature             | setandlog               | cred_access           | kode_feature
setandlog    | access            | kode_app                 | setandlog               | cred_access           | kode_feature
setandlog    | access            | kode_credential          | setandlog               | cred_access           | kode_app
setandlog    | access            | kode_privilege           | setandlog               | cred_access           | kode_app
setandlog    | access            | kode_feature             | setandlog               | cred_access           | kode_app
setandlog    | access            | kode_app                 | setandlog               | cred_access           | kode_app
setandlog    | access            | username                 | setandlog               | login                 | username

There seems to be many redudant data... When i check the query, there seems to be no diferrence in UNIQUE_CONSTRAINT_NAME at INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS table...

Can someone help me with this issue...?

Upvotes: 3

Views: 599

Answers (1)

Patrick
Patrick

Reputation: 32161

The issue you have here is that the referenced table has a PRIMARY KEY that is composed of multiple columns:

PRIMARY KEY(kode_credential, kode_privilege, kode_feature, kode_app)

These 4 columns are joined to the other tables and then you get a Cartesian product: 4 rows for every column in every foreign key. You need to use the columns ordinal_position (of the constraint column in the referencing table - access in your case) and position_in_unique_constraint (constraint column in the referenced table - cred_access), both in the key_column_usage view. In order to pull this off, you need a self-join like so:

SELECT k1.table_schema,
       k1.table_name,
       k1.column_name,
       k2.table_schema AS referenced_table_schema,
       k2.table_name AS referenced_table_name,
       k2.column_name AS referenced_column_name
FROM information_schema.key_column_usage k1
JOIN information_schema.referential_constraints fk USING (constraint_schema, constraint_name)
JOIN information_schema.key_column_usage k2
  ON k2.constraint_schema = fk.unique_constraint_schema
 AND k2.constraint_name = fk.unique_constraint_name
 AND k2.ordinal_position = k1.position_in_unique_constraint;

It's rather "magical" that MySQL can do this without specific joins like the above. Makes you wonder what other under-the-hood things could be going on...

Upvotes: 3

Related Questions