Thread: [GENERAL] What is the proper query

[GENERAL] What is the proper query

From

Igor Korot

Date:

22 August 2017, 07:08:44

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.

Re: [GENERAL] What is the proper query

From

Igor Korot

Date:

22 August 2017, 18:43:43

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.

Re: [GENERAL] What is the proper query

From

"David G. Johnston"

Date:

22 August 2017, 19:06:57

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

David J.

Re: [GENERAL] What is the proper query

From

Igor Korot

Date:

22 August 2017, 19:42:36

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

Re: [GENERAL] What is the proper query

From

Melvin Davidson

Date:

22 August 2017, 19:55:26

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

Re: [GENERAL] What is the proper query

From

"David G. Johnston"

Date:

22 August 2017, 20:03:47

On Mon, Aug 21, 2017 at 9:08 PM, Igor Korot <ikorot01@gmail.com> 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

From

Igor Korot

Date:

22 August 2017, 21:14:32

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

From

Igor Korot

Date:

23 August 2017, 01:18:07

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 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):

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

From

Igor Korot

Date:

23 August 2017, 16:54:57

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