Subject: Re: Query about foreign key details for php framework
To: David Binney <donkeysoft@gmail.com>, pgsql-sql@postgresql.org
References: 
 <CAN123gmPUc2vutkE+H0FyPJSyf+xkHam2yuiGC2U8BTin6kaOA@mail.gmail.com>
From: Adrian Klaver <adrian.klaver@aklaver.com>
Message-ID: <56CFA136.4000508@aklaver.com>
Date: Thu, 25 Feb 2016 16:49:58 -0800
User-Agent: Mozilla/5.0 (X11; Linux i686; rv:38.0) Gecko/20100101
 Thunderbird/38.6.0
MIME-Version: 1.0
In-Reply-To: 
 <CAN123gmPUc2vutkE+H0FyPJSyf+xkHam2yuiGC2U8BTin6kaOA@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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.klaver@aklaver.com


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql