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 16:49:58 -0800
Message-ID: <56CFA136.4000508@aklaver.com> (raw)
In-Reply-To: <CAN123gmPUc2vutkE+H0FyPJSyf+xkHam2yuiGC2U8BTin6kaOA@mail.gmail.com>
References: <CAN123gmPUc2vutkE+H0FyPJSyf+xkHam2yuiGC2U8BTin6kaOA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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.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: <56CFA136.4000508@aklaver.com>
Permalink: ../56CFA136.4000508@aklaver.com/
Also on: postgresql.org/message-id/56CFA136.4000508@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: <56CFA136.4000508@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