agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Adrian Klaver <adrian.klaver@aklaver.com>
To: David Binney <donkeysoft@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Query about foreign key details for php framework
Date: Thu, 25 Feb 2016 19:11:06 -0800
Message-ID: <56CFC24A.6030508@aklaver.com> (raw)
In-Reply-To: <CAN123gn7AL2mSpZitFb5bvF7VCbn0fVVv4qenyQj472P4AxdpQ@mail.gmail.com>
References: <CAN123gmPUc2vutkE+H0FyPJSyf+xkHam2yuiGC2U8BTin6kaOA@mail.gmail.com>
<56CFA136.4000508@aklaver.com>
<CAN123gnvg5fC-SOBRwYT+eK6AOzfNU2+K4+AqjqJgPi9-Kh8Pw@mail.gmail.com>
<56CFA4E1.8040204@aklaver.com>
<CAN123gn7AL2mSpZitFb5bvF7VCbn0fVVv4qenyQj472P4AxdpQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
view thread (30+ messages) latest in thread
Message-ID: <56CFC24A.6030508@aklaver.com>
Permalink: ../56CFC24A.6030508@aklaver.com/
Also on: postgresql.org/message-id/56CFC24A.6030508@aklaver.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: adrian.klaver@aklaver.com, donkeysoft@gmail.com
Subject: Re: Query about foreign key details for php framework
In-Reply-To: <56CFC24A.6030508@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox