On 02/25/2016 04:38 PM, David Binney wrote: > 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??
The * in your MySQL query hides what it is you are trying retrieve.
So what information are you after?
Or to put it another way, what fails in the Postgres version?
> > -------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; > > -- > Cheers David Binney