Re: Query about foreign key details for php framework - Mailing list pgsql-sql

On 02/25/2016 05:36 PM, David Binney wrote:
> Hey Adrian,
>
> In this case the type is just being referenced from the
> "constraint_type" column in the "information_schema.table_constraints".
>
> select * from information_schema.table_constraints tc where table_name =
> 'products';
>
> Hopefully that helps

My confusion is that in your original post you said:

"... get the same dataset from mysql and postgres ..."

and what you are asking for is different datasets. Just trying to pin 
down exactly the data you need and whether it really is the same for 
MySQL and Postgres  or if it is different?

>
> On Fri, 26 Feb 2016 at 11:06 Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 02/25/2016 04:56 PM, David Binney wrote:
>      > Hey Adrian,
>      >
>      >    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
>      >
>      > Those are the needed columns, as the end resultset.
>
>     But that is different then what you are asking from the MySQL query:
>
>     http://dev.mysql.com/doc/refman/5.7/en/information-schema.html
>
>     There is no constraint_type in the information_schema tables you
>     reference in the MySQL query, unless I am missing something.
>
>      >
>      > On Fri, 26 Feb 2016 at 10:51 Adrian Klaver
>     <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>      > <mailto:adrian.klaver@aklaver.com
>     <mailto:adrian.klaver@aklaver.com>>> wrote:
>      >
>      >     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 <mailto:adrian.klaver@aklaver.com>
>     <mailto:adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>
>      >
>      > --
>      > Cheers David Binney
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
> --
> Cheers David Binney


-- 
Adrian Klaver
adrian.klaver@aklaver.com