Re: What is the proper query

Hi,

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

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

It looks like I will be able to get what I want by using pg_constraint.oid.
I will just need to check it.

Trouble is - I won't be able to connect this table to information_schema
view
so my query will become kind of ugly. But I guess I can live with that as
long
as I have what I need. ;-)

Thank you for the hint, Melvin.

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