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