Why am I getting doubles?

From: Igor Korot <ikorot01(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Why am I getting doubles?
Date: 2017-07-28 03:52:29
Message-ID: CA+FnnTzjQ4rrUnMPCDFhmZnLrQmYYtJ0Ctn1_Upxsi3fEigCUw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,
The query below should get foreign keys for a specific table:

draft=# SELECT DISTINCT kcu.ordinal_position AS ordinal,
kcu.position_in_unique_constraint AS position, tc.constraint_name AS
name, tc.constraint_schema AS schema, tc.table_name AS table,
kcu.column_name AS column, ccu.table_name AS tableName,
ccu.column_name AS columnName, rc.update_rule, rc.delete_rule FROM
information_schema.table_constraints tc,
information_schema.key_column_usage kcu,
information_schema.constraint_column_usage ccu,
information_schema.referential_constraints rc WHERE tc.constraint_name
= kcu.constraint_name AND ccu.constraint_name = tc.constraint_name AND
rc.constraint_name = tc.constraint_name AND constraint_type = 'FOREIGN
KEY' AND tc.constraint_schema = 'public' AND tc.table_name =
'leaguescorehitter';
ordinal | position | name | schema |
table | column | tablename | columnname | update_rule |
delete_rule
---------+----------+--------------------------------+--------+-------------------+----------+-----------------+------------+-------------+-------------
2 | 2 | leaguescorehitter_id_fkey1 | public |
leaguescorehitter | playerid | playersinleague | id | NO
ACTION | NO ACTION
1 | 1 | leaguescorehitter_id_fkey1 | public |
leaguescorehitter | id | playersinleague | id | NO
ACTION | NO ACTION
1 | 1 | leaguescorehitter_id_fkey1 | public |
leaguescorehitter | id | playersinleague | playerid | NO
ACTION | NO ACTION
1 | 1 | leaguescorehitter_scoreid_fkey | public |
leaguescorehitter | scoreid | scorehits | scoreid | NO
ACTION | NO ACTION
1 | 1 | leaguescorehitter_id_fkey | public |
leaguescorehitter | id | leagues | id | NO
ACTION | NO ACTION
2 | 2 | leaguescorehitter_id_fkey1 | public |
leaguescorehitter | playerid | playersinleague | playerid | NO
ACTION | NO ACTION
(6 rows)

draft=# \d leaguescorehitter
Table "public.leaguescorehitter"
Column | Type | Modifiers
----------+---------+-----------
id | integer |
playerid | integer |
scoreid | integer |
value | numeric |
Indexes:
"leaguescorehitter_playerid" btree (playerid)
Foreign-key constraints:
"leaguescorehitter_id_fkey" FOREIGN KEY (id) REFERENCES leagues(id)
"leaguescorehitter_id_fkey1" FOREIGN KEY (id, playerid) REFERENCES
playersinleague(id, playerid)
"leaguescorehitter_scoreid_fkey" FOREIGN KEY (scoreid) REFERENCES
scorehits(scoreid)

If I don't have a foreing key with 2 fields everything works fine.

Is there a reason I'm seeing duplicate records on the query above?

Thank you.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2017-07-28 04:09:46 Re: Why am I getting doubles?
Previous Message Alex Samad 2017-07-28 02:50:42 Re: Question about paritioning