Re: What is the proper query

*While the information_schema is useful, there is no substitute for
learning how to use the pg_catalog and system information functions.*

*See if this query gives you what you are looking for:*

*SELECT rel.relname, con.conname, con.contype,
con.consrc, pg_get_constraintdef(con.oid, true) FROM pg_class rel
JOIN pg_constraint con ON (con.conrelid = rel.oid)*

*ORDER by relname, contype, conname;*

On Tue, Aug 22, 2017 at 12:42 PM, Igor Korot <ikorot01(at)gmail(dot)com> wrote:

> Hi, David,
>
> On Tue, Aug 22, 2017 at 12:06 PM, David G. Johnston
> <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> > On Tue, Aug 22, 2017 at 8:43 AM, Igor Korot <ikorot01(at)gmail(dot)com> wrote:
> >>
> >> Or this is the bug in 9.1?
> >> Since it looks like there are 2 columns with the same info in 1
> >> table/view....
> >
> >
> > This old email thread sounds similar to what you are describing here.
> >
> > https://www.postgresql.org/message-id/56D0C4B8.7020200%40aklaver.com
>
> Consider following table creation command:
>
> CREATE TABLE leaguescorehitter(id integer, playerid integer, scoreid
> integer, value double, foreign key(id) references leagues(id), foreign
> key(id, playerid) references playersinleague(id, playerid), foreign
> key(scoreid) references scorehits(scoreid));
>
> There are 3 foreign keys in this table for which there are 4 rows
> displayed in my query as it should be:
>
> 1 for leagues(id)
> 1 for scorehits(scoreid)
> 2 for playersinleague(id,playerid) - 1 row per field
>
> However what I would expect to see is:
>
> [code]
> ordinal_position | position_in_unique_constraint
> 0 1
> - this is for leagues(id)
> 1 1
> 1 2
> - those 2 are for
> playersinleague(id,playerid)
> 2 1
> - this is for scorehits(scoreid)
> [/code]
>
> Instead I got ordinal_positionv = position_in_unique_constraints and can't
> tell
> which constraint is which, or more precisely, when the one ends and
> second starts.
>
> Hopefully this above will not be mangled and the spacing will be kept.
>
> Thank you.
>
> >
> > David J.
> >
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.