Hey guys,
I am having a tricky problem which I have not needed to solve before. Basically one of the php frameworks I am using needs to get the same dataset from mysql and postgres but I am not sure how to do the joins.
Below i have the mysql version of the query which work ok, and after that i have my attempt at the postgresql version, which is not joined correctly. Any help would be greatly appreciated, and in the meantime i will keep guessing which columns need to be joined for those three tables, but I am thinking there could be a view or something to solve my problem straight away??
-------mysql working version----------
SELECT
*
FROM
information_schema.key_column_usage AS kcu
INNER JOIN information_schema.referential_constraints AS rc ON (
kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
AND kcu.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
)
WHERE
kcu.TABLE_SCHEMA = 'timetable'
AND kcu.TABLE_NAME = 'issues'
AND rc.TABLE_NAME = 'issues'
---- postgresql partial working version--------------
select
rc.constraint_name AS name,
tc.constraint_type AS type,
kcu.column_name,
rc.match_option AS match_type,
rc.update_rule AS on_update,
rc.delete_rule AS on_delete,
kcu.table_name AS references_table,
kcu.column_name AS references_field,
kcu.ordinal_position
FROM
(select distinct * from information_schema.referential_constraints) rc
JOIN information_schema.key_column_usage kcu
ON kcu.constraint_name = rc.constraint_name
AND kcu.constraint_schema = rc.constraint_schema
JOIN information_schema.table_constraints tc ON tc.constraint_name = rc.constraint_name
AND tc.constraint_schema = rc.constraint_schema
AND tc.constraint_name = rc.constraint_name
AND tc.table_schema = rc.constraint_schema
WHERE
kcu.table_name = 'issues'
AND rc.constraint_schema = 'public'
AND tc.constraint_type = 'FOREIGN KEY'
ORDER BY
rc.constraint_name,
cu.ordinal_position;
