This was tested on v7.0.1-0.
Inner table
Code: Select all
CREATE TABLE public.table1 (
id int PRIMARY KEY,
val char(1)
);
INSERT INTO public.table1 VALUES (1, 'a');
INSERT INTO public.table1 VALUES (1, 'b');
SELECT * FROM public.table1;
id | val
----+-----
1 | a
1 | b
(2 rows)
SELECT ANALYZE_CONSTRAINTS('public.table1');
Schema Name | Table Name | Column Names | Constraint Name | Constraint Type | Column Values
-------------+------------+--------------+-----------------+-----------------+---------------
public | table1 | id | C_PRIMARY | PRIMARY | ('1')
(1 row)
When table1 is joined to a primary key column in table2, no error is thrown.
Code: Select all
CREATE TABLE public.table2 (
id int PRIMARY KEY
);
INSERT INTO public.table2 VALUES (1);
SELECT a.val
FROM public.table1 a
JOIN public.table2 b
ON a.id = b.id;
val
-----
a
b
(2 rows)
When table1 is joined to a non-key column in table2, the error is thrown.
Code: Select all
DROP TABLE public.table2 CASCADE;
CREATE TABLE public.table2 (
id int
);
INSERT INTO public.table2 VALUES (1);
SELECT a.val
FROM public.table1 a
JOIN public.table2 b
ON a.id = b.id;
ERROR 3149: Duplicate primary/unique key detected in join [(public.table2 x public.table1) using table2_b0 and table1_b0 (PATH ID: 1)]; value [1]
When table1 is joined to a primary key column containing the same duplicates in table2, the error is thrown.
Code: Select all
DROP TABLE public.table2 CASCADE;
CREATE TABLE public.table2 (
id int PRIMARY KEY
);
INSERT INTO public.table2 VALUES (1);
INSERT INTO public.table2 VALUES (1);
SELECT a.val
FROM public.table1 a
JOIN public.table2 b
ON a.id = b.id;
ERROR 3149: Duplicate primary/unique key detected in join [(public.table1 x public.table2) using table1_b0 and table2_b0 (PATH ID: 1)]; value [1]