Postgres 9.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE projects (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);
CREATE TABLE entries(
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    project_id INTEGER NOT NULL REFERENCES projects
);
CREATE UNIQUE INDEX entries__name_un ON entries(name);
INSERT INTO projects(name)
VALUES
    ('Alpha'),
    ('Beta'),
    ('Gamma')
;
SELECT * FROM projects;
3 rows affected



id
name


1
Alpha

2
Beta

3
Gamma


INSERT INTO entries(name, project_id)
VALUES
    ('a1', 1),
    (' a1', 1),
    ('a2',1),
    ('b1',2),
    ('b2',2),
    ('b3', 2),
    ('b3 ', 2),
    ('a1 ', 2),
    ('g1', 3),
    ('g2 ', 3),
    ('g2', 3),
    ('u4',1),
    ('u4 ', 2),
    (' u4', 3)
;
SELECT * FROM entries;
14 rows affected



id
name
project_id


1
a1
1

2
 a1
1

3
a2
1

4
b1
2

5
b2
2

6
b3
2

7
b3
2

8
a1
2

9
g1
3

10
g2
3

11
g2
3

12
u4
1

13
u4
2

14
 u4
3


WITH all_entries AS (
    SELECT
        e.id AS id,
        trim(e.name) AS name,
        e.project_id AS project_id
    FROM
        entries e
),
duplicates_count AS (
    SELECT
       name,
       count(1) AS matches
    FROM all_entries
    GROUP BY name
    HAVING count(1) > 1
)
SELECT
    name,
    matches,
    ARRAY (SELECT e.id FROM all_entries e WHERE e.name = dc.name) AS duplicates_ids,
    ARRAY (SELECT e.project_id FROM all_entries e WHERE e.name = dc.name) AS duplicates_projects
FROM duplicates_count dc
;


name
matches
duplicates_ids
duplicates_projects


a1
3
{1,2,8}
{1,1,2}

u4
3
{12,13,14}
{1,2,3}

g2
2
{10,11}
{3,3}

b3
2
{6,7}
{2,2}

id	name	project_id
1	a1	1
2	a1	1
3	a2	1
4	b1	2
5	b2	2
6	b3	2
7	b3	2
8	a1	2
9	g1	3
10	g2	3
11	g2	3
12	u4	1
13	u4	2
14	u4	3

name	matches	duplicates_ids	duplicates_projects
a1	3	{1,2,8}	{1,1,2}
u4	3	{12,13,14}	{1,2,3}
g2	2	{10,11}	{3,3}
b3	2	{6,7}	{2,2}

id	name	project_id
1	a1	1
2	a1	1
3	a2	1
4	b1	2
5	b2	2
6	b3	2
7	b3	2
8	a1	2
9	g1	3
10	g2	3
11	g2	3
12	u4	1
13	u4	2
14	u4	3

id	name	project_id
1	a1	1
2	a1	1
3	a2	1
4	b1	2
5	b2	2
6	b3	2
7	b3	2
8	a1	2
9	g1	3
10	g2	3
11	g2	3
12	u4	1
13	u4	2
14	u4	3