Re: Query about foreign key details for php framework - Mailing list pgsql-sql

Its not really whats missing, but that query will pretty much return every foreign key in the database, rather than the ones for that particular table 'issues'. So in my case, instead of returning the 5 foreign keys for that table, it will return lots of rows < 1k. However, the mysql query will return just the 5 rows, this is the main reason i suspect the joins to not be correct, unless i am still missing something.

I have added a sample below to show you what i mean. The first shows the 5fk's which can be seen in the table_constraints, but when i run the full query i will get (2622 rows) : 

testdb=# \d issues

 id                         | integer                     | not null default nextval('issues_id_seq'::regclass)

 issue_number               | smallint                    |

 description_brief          | character varying(2000)     |

 description_full           | text                        |

 active                     | smallint                    | default 1

 ordering                   | smallint                    | default 0

 issue_status_option_id     | bigint                      | not null

 issue_priority_option_id   | bigint                      | not null

 issue_complexity_option_id | bigint                      | not null

 issue_ux_effect_id         | bigint                      | not null

 issue_stage_id             | bigint                      | not null

 created                    | timestamp without time zone |

 modified                   | timestamp without time zone |

testdb=# select * from information_schema.table_constraints where table_name = 'issues';

 testdb          | public            | issues_pkey            | testdb     | public       | issues     | PRIMARY KEY     | NO            | NO

 testdb          | public            | fk1                    | testdb     | public       | issues     | FOREIGN KEY     | NO            | NO

 testdb          | public            | fk2                    | testdb     | public       | issues     | FOREIGN KEY     | NO            | NO

 testdb          | public            | fk3                    | testdb     | public       | issues     | FOREIGN KEY     | NO            | NO

 testdb          | public            | fk4                    | testdb     | public       | issues     | FOREIGN KEY     | NO            | NO

 testdb          | public            | fk5                    | testdb     | public       | issues     | FOREIGN KEY     | NO            | NO

 testdb          | public            | 2200_77475_1_not_null  | testdb     | public       | issues     | CHECK           | NO            | NO

 testdb          | public            | 2200_77475_7_not_null  | testdb     | public       | issues     | CHECK           | NO            | NO

 testdb          | public            | 2200_77475_8_not_null  | testdb     | public       | issues     | CHECK           | NO            | NO

 testdb          | public            | 2200_77475_9_not_null  | testdb     | public       | issues     | CHECK           | NO            | NO

 testdb          | public            | 2200_77475_10_not_null | testdb     | public       | issues     | CHECK           | NO            | NO

 testdb          | public            | 2200_77475_11_not_null | testdb     | public       | issues     | CHECK           | NO            | NO

On Fri, 26 Feb 2016 at 20:08 Stuart <sfbarbee@gmail.com> wrote:

Off hand I don't see anything wrong with the query.  You will probably need to examine what specifically is missing in the results and check table and constraint definitions.

On Feb 26, 2016 13:45, "David Binney" <donkeysoft@gmail.com> wrote:

Those joins used in the source query, do not seem enough from my observations. It is joining on schema + constraint name, which can match multiple constraints as it is not unique. If i can work out how to join all three tables correctly, that would probably be the answer. However, i don't want to ignore the fact that there might be a better way to get those values in the select from alternative tables. I just don't know enough about the schema tables to be sure.

  (select distinct * from information_schema.referential_constraints) rc

  JOIN information_schema.key_column_usage kcu 

  ON  kcu.constraint_name   = rc.constraint_name

  AND kcu.constraint_schema = rc.constraint_schema

On Fri, 26 Feb 2016 at 19:13 Stuart <sfbarbee@gmail.com> wrote:

David,

Can you elaborate.  Can you explain what you mean by "joins to those tables are incorrect" and "not joining correctly"? How are you determining this incorrectness? 

If you believe results are incorrect, what is incorrect/missing and why? The answer will be in how the tables and constraints are defined.

On Feb 26, 2016 11:29, "David Binney" <donkeysoft@gmail.com> wrote:

Hey Stuart, 

Well I should be more specific that it is not valid at all, because the joins to those tables are incorrect. When joining between those three tables it is not joining correctly, which means the data is potentially invalid, however its close, looking if you know what I mean. 

On Fri, 26 Feb 2016 at 15:18 Stuart <sfbarbee@gmail.com> wrote:

David,

Just to toss my 2 cents in here, the initial question to your original post was, "what's broken with the postgresql version of select"? Your statement says it's partially working. What part is working and what part isn't working? Do you get errors from the postgresql select or does it just not give you all that you need?

Stuart

On Feb 26, 2016 04:38, "David Binney" <donkeysoft@gmail.com> wrote:

Hey guys, 

I am having a tricky problem which I have not needed to solve before. Basically one of the php frameworks I am using needs to get the same dataset from mysql and postgres but I am not sure how to do the joins. 

Below i have the mysql version of the query which work ok, and after that i have my attempt at the postgresql version, which is not joined correctly. Any help would be greatly appreciated, and in the meantime i will keep guessing which columns need to be joined for those three tables, but I am thinking there could be a view or something to solve my problem straight away?? 

-------mysql working version----------

SELECT 

  * 

FROM 

  information_schema.key_column_usage AS kcu 

  INNER JOIN information_schema.referential_constraints AS rc ON (

    kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME 

    AND kcu.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA

  ) 

WHERE 

  kcu.TABLE_SCHEMA = 'timetable' 

  AND kcu.TABLE_NAME = 'issues' 

  AND rc.TABLE_NAME = 'issues'

---- postgresql partial working version--------------

select  

  rc.constraint_name AS name,

  tc.constraint_type AS type,

  kcu.column_name,

  rc.match_option AS match_type,

  rc.update_rule AS on_update,

  rc.delete_rule AS on_delete,

  kcu.table_name AS references_table,

  kcu.column_name AS references_field,

  kcu.ordinal_position

FROM

  (select distinct * from information_schema.referential_constraints) rc

  JOIN information_schema.key_column_usage kcu 

  ON  kcu.constraint_name   = rc.constraint_name

  AND kcu.constraint_schema = rc.constraint_schema

  JOIN information_schema.table_constraints tc ON tc.constraint_name = rc.constraint_name

  AND tc.constraint_schema = rc.constraint_schema

  AND tc.constraint_name = rc.constraint_name

  AND tc.table_schema = rc.constraint_schema

WHERE

  kcu.table_name = 'issues'

  AND rc.constraint_schema = 'public'

  AND tc.constraint_type = 'FOREIGN KEY'

ORDER BY

  rc.constraint_name,

  cu.ordinal_position;

--

Cheers David Binney

--

Cheers David Binney

--

Cheers David Binney

--

Cheers David Binney