Thread: [GENERAL] What is the proper query
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.
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 <ikorot01@gmail.com> 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.
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.
David J.
Hi, David, On Tue, Aug 22, 2017 at 12:06 PM, David G. Johnston <david.g.johnston@gmail.com> wrote: > On Tue, Aug 22, 2017 at 8:43 AM, Igor Korot <ikorot01@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. >
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:the pg_catalog and system information functions.
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 <ikorot01@gmail.com> wrote:
Hi, David,
On Tue, Aug 22, 2017 at 12:06 PM, David G. Johnston
<david.g.johnston@gmail.com> wrote:
> On Tue, Aug 22, 2017 at 8:43 AM, Igor Korot <ikorot01@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.![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
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.
Hi, Melvin,
On Tue, Aug 22, 2017 at 12:55 PM, Melvin Davidson <melvin6925@gmail.com> wrote:
While the information_schema is useful, there is no substitute for learning how to useSee if this query gives you what you are looking for:
the pg_catalog and system information functions.
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<int, std::vector<FKField> >;
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 <ikorot01@gmail.com> wrote:Hi, David,
On Tue, Aug 22, 2017 at 12:06 PM, David G. Johnston
<david.g.johnston@gmail.com> wrote:
> On Tue, Aug 22, 2017 at 8:43 AM, Igor Korot <ikorot01@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%40akl aver.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.
Melvin et al,
On Tue, Aug 22, 2017 at 12:55 PM, Melvin Davidson <melvin6925@gmail.com> wrote:
While the information_schema is useful, there is no substitute for learning how to useSee if this query gives you what you are looking for:
the pg_catalog and system information functions.
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
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@gmail.com> wrote:Hi, David,
On Tue, Aug 22, 2017 at 12:06 PM, David G. Johnston
<david.g.johnston@gmail.com> wrote:
> On Tue, Aug 22, 2017 at 8:43 AM, Igor Korot <ikorot01@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%40akl aver.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.
Hi,
On Tue, Aug 22, 2017 at 6:18 PM, Igor Korot <ikorot01@gmail.com> wrote:
Melvin et al,On Tue, Aug 22, 2017 at 12:55 PM, Melvin Davidson <melvin6925@gmail.com> wrote:While the information_schema is useful, there is no substitute for learning how to useSee if this query gives you what you are looking for:
the pg_catalog and system information functions.
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|NONECan 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@gmail.com> wrote:Hi, David,
On Tue, Aug 22, 2017 at 12:06 PM, David G. Johnston
<david.g.johnston@gmail.com> wrote:
> On Tue, Aug 22, 2017 at 8:43 AM, Igor Korot <ikorot01@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%40akl aver.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.