Re: [GENERAL] What is the proper query

[Igor Korot]

Hi,

On Tue, Aug 22, 2017 at 6:18 PM, Igor Korot  wrote:

> Melvin et al,
>
> On Tue, Aug 22, 2017 at 12:55 PM, Melvin Davidson 
> 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  wrote:
>>
>>> Hi, David,
>>>
>>> On Tue, Aug 22, 2017 at 12:06 PM, David G. Johnston
>>>  wrote:
>>> > On Tue, Aug 22, 2017 at 8:43 AM, Igor Korot 
>>> 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.
>>
>
>

Re: [GENERAL] What is the proper query

[Igor Korot]

Melvin et al,

On Tue, Aug 22, 2017 at 12:55 PM, Melvin Davidson 
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  wrote:
>
>> Hi, David,
>>
>> On Tue, Aug 22, 2017 at 12:06 PM, David G. Johnston
>>  wrote:
>> > On Tue, Aug 22, 2017 at 8:43 AM, Igor Korot  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.
>

Re: [GENERAL] What is the proper query

[Igor Korot]

Hi, Melvin,

On Tue, Aug 22, 2017 at 12:55 PM, Melvin Davidson 
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;*
>

Here is what I'm after:

select x.ordinal_position AS pos, x.position_in_unique_constraint AS
field_pos, c.constraint_name AS name, x.table_schema as schema,
x.table_name AS table, x.column_name AS column, y.table_schema as
ref_schema, y.table_name as ref_table, y.column_name as ref_column,
c.update_rule, c.delete_rule from
information_schema.referential_constraints c,
information_schema.key_column_usage x, information_schema.key_column_usage
y where x.constraint_name = c.constraint_name and y.ordinal_position =
x.position_in_unique_constraint and y.constraint_name =
c.unique_constraint_name AND x.table_schema = $1 AND x.table_name = $2
order by c.constraint_name, x.ordinal_position;

Then in my C++ code:

std::map;

foreign_keys[pos].push_back( new FKField( field_pos, name, column,
ref_schema, ref_table, ref_column, update_rule, delete_rule ) );

This is my target.

Thank you.


>
>
> On Tue, Aug 22, 2017 at 12:42 PM, Igor Korot  wrote:
>
>> Hi, David,
>>
>> On Tue, Aug 22, 2017 at 12:06 PM, David G. Johnston
>>  wrote:
>> > On Tue, Aug 22, 2017 at 8:43 AM, Igor Korot  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.
>

Re: [GENERAL] What is the proper query

[David G. Johnston]

On Mon, Aug 21, 2017 at 9:08 PM, Igor Korot  wrote:

> Hi, ALL,
> draft=# SELECT * FROM information_schema.key_column_usage
>
​[...]​


> There are 3 foreign keys in that table.
>
> Is there a way to get values of 0, 1, 1, and 2 for the ordinal position?
>

Not using the key_column_usage view.  What that view is doing is basically
saying (my understanding from reading the docs, not testing it out):

CREATE TABLE tbl_pk
UNIQUE (col1, col2)​

CREATE TABLE tbl_fk
FOREIGN (col2, col1) REFERENCES tbl_pk (col1, col2)

Now your ordinal/position rows would be:

(1, 2)
(2, 1)

instead of:

(1, 1)
(2, 2)

if you had defined the FK and PK with the same column names in the same
order, like is done almost always and like you did in your example.

If you want to enumerate constraints you need to use a different
information_schema view or, as Melvin showed, use pg_catalog.  I'm not
fluent enough to provide examples.  If you provide the question/problem you
are trying to resolve others will likely offer suggestions.

David J.

Re: [GENERAL] What is the proper query

[Melvin Davidson]

*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  wrote:

> Hi, David,
>
> On Tue, Aug 22, 2017 at 12:06 PM, David G. Johnston
>  wrote:
> > On Tue, Aug 22, 2017 at 8:43 AM, Igor Korot  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.

Re: [GENERAL] What is the proper query

[Igor Korot]

Hi, David,

On Tue, Aug 22, 2017 at 12:06 PM, David G. Johnston
 wrote:
> On Tue, Aug 22, 2017 at 8:43 AM, Igor Korot  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

Re: [GENERAL] What is the proper query

[David G. Johnston]

On Tue, Aug 22, 2017 at 8:43 AM, Igor Korot  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

David J.

Re: [GENERAL] What is the proper query

[Igor Korot]

Or this is the bug in 9.1?
Since it looks like there are 2 columns with the same info in 1 table/view

Thank you.


On Tue, Aug 22, 2017 at 12:08 AM, Igor Korot  wrote:
> Hi, ALL,
> draft=# SELECT * FROM information_schema.key_column_usage WHERE
> table_schema = 'public' AND table_name = 'leaguescorehitter';
>  constraint_catalog | constraint_schema |constraint_name
>   | table_catalog | table_schema |table_name | column_name |
> ordinal_position | position_in_unique_constraint
> +---++---+--+---+-+--+---
>  draft  | public| leaguescorehitter_id_fkey
>   | draft | public   | leaguescorehitter | id  |
>  1 | 1
>  draft  | public| leaguescorehitter_id_fkey1
>   | draft | public   | leaguescorehitter | id  |
>  1 | 1
>  draft  | public| leaguescorehitter_id_fkey1
>   | draft | public   | leaguescorehitter | playerid|
>  2 | 2
>  draft  | public|
> leaguescorehitter_scoreid_fkey | draft | public   |
> leaguescorehitter | scoreid |1 |
>   1
> (4 rows)
>
> There are 3 foreign keys in that table.
>
> Is there a way to get values of 0, 1, 1, and 2 for the ordinal position?
>
> Thank you.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general