From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | David Binney <donkeysoft(at)gmail(dot)com>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Query about foreign key details for php framework |
Date: | 2016-02-26 00:49:58 |
Message-ID: | 56CFA136.4000508@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | David Binney | 2016-02-26 00:56:32 | Re: Query about foreign key details for php framework |
Previous Message | David Binney | 2016-02-26 00:38:20 | Query about foreign key details for php framework |