From: David Binney <donkeysoft@gmail.com>
To: Adrian Klaver <adrian.klaver@aklaver.com>
To: pgsql-sql@postgresql.org
Subject: Re: Query about foreign key details for php framework
Date: Fri, 26 Feb 2016 01:36:43 +0000
Message-ID: <CAN123gn7AL2mSpZitFb5bvF7VCbn0fVVv4qenyQj472P4AxdpQ@mail.gmail.com> (raw)
In-Reply-To: <56CFA4E1.8040204@aklaver.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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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
On Fri, 26 Feb 2016 at 11:06 Adrian Klaver <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>> 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>
> >
> > --
> > Cheers David Binney
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
--
Cheers David Binney
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: donkeysoft@gmail.com, adrian.klaver@aklaver.com
Subject: Re: Query about foreign key details for php framework
In-Reply-To: <CAN123gn7AL2mSpZitFb5bvF7VCbn0fVVv4qenyQj472P4AxdpQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox