Hi, On Tue, Aug 22, 2017 at 6:18 PM, Igor Korot <ikoro...@gmail.com> wrote:
> Melvin et al, > > On Tue, Aug 22, 2017 at 12:55 PM, Melvin Davidson <melvin6...@gmail.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 <ikoro...@gmail.com> wrote: >> >>> Hi, David, >>> >>> On Tue, Aug 22, 2017 at 12:06 PM, David G. Johnston >>> <david.g.johns...@gmail.com> wrote: >>> > On Tue, Aug 22, 2017 at 8:43 AM, Igor Korot <ikoro...@gmail.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@postgresql.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. >> > >
