Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1aZ7M0-0002M8-Ug for pgsql-sql@arkaria.postgresql.org; Fri, 26 Feb 2016 01:38:01 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1aZ7Ly-0002LX-PO for pgsql-sql@arkaria.postgresql.org; Fri, 26 Feb 2016 01:37:58 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1aZ7Kz-0001D2-2E for pgsql-sql@postgresql.org; Fri, 26 Feb 2016 01:36:57 +0000 Received: from mail-vk0-x230.google.com ([2607:f8b0:400c:c05::230]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1aZ7Kv-00058k-Vr for pgsql-sql@postgresql.org; Fri, 26 Feb 2016 01:36:55 +0000 Received: by mail-vk0-x230.google.com with SMTP id k196so65476125vka.0 for ; Thu, 25 Feb 2016 17:36:53 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :content-type; bh=v/pulXgXuAGwzCNp0jpbzXI+LtM/SDfXtXAiBo5jIRI=; b=yI8iNdfpy95Ran/FWLM10PsOsXEC2wCLcMZ2PABNwFw6ptH1i4yCW6jpVutHMzy1Jz Bcqwx5qebrjDvViNejtN5lxixuQ3hA+ocN+Vqo9iZyNQpu8bdWYJE7n1nksCWyShQzIu gnNpbAkzDNtA0MuCj+2GUssY+qSJiARxhDN0N+XNw+mgf3Cn/JCfkqiRYcWuyTSQSNVE lrvGJmOe9gVbxSex0/e9kaSYDKBckmo1DuRFZaZsgRe1XxvnNku/x8ilUZCH3IcXJLa/ JGVvSmtbY+5lT9YPvBlVToDP1GdtIcvrQvkk0686zVj9lal4rHBU8tNWEd9V/g4qlj1e vNTw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:content-type; bh=v/pulXgXuAGwzCNp0jpbzXI+LtM/SDfXtXAiBo5jIRI=; b=JoKfKuxyvXsuAk8+5Nx1gqlEHcXlohx8GOI6MuRMHQs3k/PSlzJE/omJAQpyx2FAfm tWzfQGwwDOYIZNtRMUy54b9qgAoS/eQYIFx/dFd0jVdUcrJx3qFHt4I9o1zpaGpWMi4N LJejL2zOtpkGnQdFKxWLtfZbCIgf99a71EKpBYRswIR47hRvVuu8DYYeiJPcLNkGY1s0 mNX5NOiw8zIlC/3RNRIsINTrpAbbG/0t1kFFOqT19Yx6KxjsyFHlwSKdllkLaeiCeuQh hPxdBQDKeUtMFeJAmRSzxYeyF3y+WYxEH+6Cpmoxe4DG7X14MBb4E9Zy5ar25AfrHLw2 eD2Q== X-Gm-Message-State: AG10YOTTpE088cGHXupmX/MaPW6r0v14wM0dDTK6MEY4ZswY/iRUERRmF0uKyOTDMNsu1VHKZKYfuxCp5nImjw== X-Received: by 10.31.52.195 with SMTP id b186mr35981767vka.39.1456450613042; Thu, 25 Feb 2016 17:36:53 -0800 (PST) MIME-Version: 1.0 References: <56CFA136.4000508@aklaver.com> <56CFA4E1.8040204@aklaver.com> In-Reply-To: <56CFA4E1.8040204@aklaver.com> From: David Binney Date: Fri, 26 Feb 2016 01:36:43 +0000 Message-ID: Subject: Re: Query about foreign key details for php framework To: Adrian Klaver , pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=001a1143e5426c6522052ca25581 X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --001a1143e5426c6522052ca25581 Content-Type: text/plain; charset=UTF-8 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 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 > > 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 > > > > -- > > Cheers David Binney > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > -- Cheers David Binney --001a1143e5426c6522052ca25581 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hey Adrian,=C2=A0

In this case the type= is just being referenced from the "constraint_type" column in th= e "information_schema.table_constraints".=C2=A0

select * from information_schema.table_constraints tc where ta= ble_name =3D 'products';

Hopefully t= hat helps=C2=A0

= 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,
>
>=C2=A0 =C2=A0 rc.constraint_name AS name,
>=C2=A0 =C2=A0 tc.constraint_type AS type,
>=C2=A0 =C2=A0 kcu.column_name,
>=C2=A0 =C2=A0 rc.match_option AS match_type,
>=C2=A0 =C2=A0 rc.update_rule AS on_update,
>=C2=A0 =C2=A0 rc.delete_rule AS on_delete,
>=C2=A0 =C2=A0 kcu.table_name AS references_table,
>=C2=A0 =C2=A0 kcu.column_name AS references_field,
>=C2=A0 =C2=A0 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:
>
>=C2=A0 =C2=A0 =C2=A0On 02/25/2016 04:38 PM, David Binney wrote:
>=C2=A0 =C2=A0 =C2=A0 > Hey guys,
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > I am having a tricky problem which I have not= needed to solve before.
>=C2=A0 =C2=A0 =C2=A0 > Basically one of the php frameworks I am usin= g needs to get the same
>=C2=A0 =C2=A0 =C2=A0 > dataset from mysql and postgres but I am not = sure how to do the
>=C2=A0 =C2=A0 =C2=A0joins.
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > Below i have the mysql version of the query w= hich work ok, and after
>=C2=A0 =C2=A0 =C2=A0 > that i have my attempt at the postgresql vers= ion, which is not joined
>=C2=A0 =C2=A0 =C2=A0 > correctly. Any help would be greatly apprecia= ted, and in the
>=C2=A0 =C2=A0 =C2=A0meantime i
>=C2=A0 =C2=A0 =C2=A0 > will keep guessing which columns need to be j= oined for those three
>=C2=A0 =C2=A0 =C2=A0 > tables, but I am thinking there could be a vi= ew or something to
>=C2=A0 =C2=A0 =C2=A0solve my
>=C2=A0 =C2=A0 =C2=A0 > problem straight away??
>
>=C2=A0 =C2=A0 =C2=A0The * in your MySQL query hides what it is you are = trying retrieve.
>
>=C2=A0 =C2=A0 =C2=A0So what information are you after?
>
>=C2=A0 =C2=A0 =C2=A0Or to put it another way, what fails in the Postgre= s version?
>
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > -------mysql working version----------
>=C2=A0 =C2=A0 =C2=A0 > SELECT
>=C2=A0 =C2=A0 =C2=A0 > *
>=C2=A0 =C2=A0 =C2=A0 > FROM
>=C2=A0 =C2=A0 =C2=A0 > information_schema.key_column_usage AS kcu >=C2=A0 =C2=A0 =C2=A0 > INNER JOIN information_schema.referential_con= straints AS rc ON (
>=C2=A0 =C2=A0 =C2=A0 > kcu.CONSTRAINT_NAME =3D rc.CONSTRAINT_NAME >=C2=A0 =C2=A0 =C2=A0 > AND kcu.CONSTRAINT_SCHEMA =3D rc.CONSTRAINT_S= CHEMA
>=C2=A0 =C2=A0 =C2=A0 > )
>=C2=A0 =C2=A0 =C2=A0 > WHERE
>=C2=A0 =C2=A0 =C2=A0 > kcu.TABLE_SCHEMA =3D 'timetable'
>=C2=A0 =C2=A0 =C2=A0 > AND kcu.TABLE_NAME =3D 'issues'
>=C2=A0 =C2=A0 =C2=A0 > AND rc.TABLE_NAME =3D 'issues'
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > ---- postgresql partial working version------= --------
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > select
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 rc.constraint_name AS name,
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 tc.constraint_type AS type,
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 kcu.column_name,
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 rc.match_option AS match_type, >=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 rc.update_rule AS on_update,
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 rc.delete_rule AS on_delete,
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 kcu.table_name AS references_tab= le,
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 kcu.column_name AS references_fi= eld,
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 kcu.ordinal_position
>=C2=A0 =C2=A0 =C2=A0 > FROM
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 (select distinct * from
>=C2=A0 =C2=A0 =C2=A0information_schema.referential_constraints) rc
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 JOIN information_schema.key_colu= mn_usage kcu
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 ON=C2=A0 kcu.constraint_name=C2= =A0 =C2=A0=3D rc.constraint_name
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 AND kcu.constraint_schema =3D rc= .constraint_schema
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 JOIN information_schema.table_co= nstraints tc ON
>=C2=A0 =C2=A0 =C2=A0tc.constraint_name =3D
>=C2=A0 =C2=A0 =C2=A0 > rc.constraint_name
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 AND tc.constraint_schema =3D rc.= constraint_schema
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 AND tc.constraint_name =3D rc.co= nstraint_name
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 AND tc.table_schema =3D rc.const= raint_schema
>=C2=A0 =C2=A0 =C2=A0 > WHERE
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 kcu.table_name =3D 'issues&#= 39;
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 AND rc.constraint_schema =3D = 9;public'
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 AND tc.constraint_type =3D '= FOREIGN KEY'
>=C2=A0 =C2=A0 =C2=A0 > ORDER BY
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 rc.constraint_name,
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 cu.ordinal_position;
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > --
>=C2=A0 =C2=A0 =C2=A0 > Cheers David Binney
>
>
>=C2=A0 =C2=A0 =C2=A0--
>=C2=A0 =C2=A0 =C2=A0Adrian Klaver
>=C2=A0 =C2=A0 =C2=A0adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com><= br> >
> --
> Cheers David Binney


--
Adrian Klaver
adrian.klave= r@aklaver.com
--
Cheers Da= vid Binney
--001a1143e5426c6522052ca25581--