Why am I getting doubles?
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Igor Korot <ikorot01@gmail.com> writes:
Is there a reason I'm seeing duplicate records on the query above?
Your example isn't complete, but I think the problem is your
WHERE clause isn't equating enough columns. For instance,
if I do
db=# create table pp(f1 int, f2 int, primary key (f1,f2));
CREATE TABLE
that produces two rows in information_schema.key_column_usage:
db | public | pp_pkey | db | public | pp | f1 | 1 |
db | public | pp_pkey | db | public | pp | f2 | 2 |
Your WHERE clause can't tell the difference between these.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general