Re: What is the proper query

Melvin et al,

On Tue, Aug 22, 2017 at 12:55 PM, Melvin Davidson <melvin6925(at)gmail(dot)com>
wrote:

>
>
>
> *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;*
>

I tried your query, but its not really what I'm looking for.

This is what I'm looking for (taken from SQLite shell):

sqlite> PRAGMA foreign_key_list(leaguescorehitter);
id|seq|table|from|to|on_update|on_delete|match
0|0|scorehits|scoreid|scoreid|NO ACTION|NO ACTION|NONE
1|0|playersinleague|id|id|NO ACTION|NO ACTION|NONE
1|1|playersinleague|playerid|playerid|NO ACTION|NO ACTION|NONE
2|0|leagues|id|id|NO ACTION|NO ACTION|NONE

Can I get something from PostgreSQL?

Thank you.

>
> 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.
>