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