PostgreSQL: custom logic for determining distinct rows?

Question

Here's my problem. Suppose I have a table called persons containing, among other things, fields for the person's name and national identification number, with the latter being optional. There can be multiple rows for each actual person.

Now suppose I want to select exactly one row for each actual person. For the purposes of the application, two rows are considered to refer to the same person if a) their ID numbers match, or b) their names match and the ID number of one or both is NULL. SELECT DISTINCT is no good here: I cannot do a DISTINCT ON (name, id) because then two rows with the same name where the ID of one is NULL wouldn't match (which is incorrect, they should be considered the same). I cannot do a DISTINCT ON (name) because then rows with the same name but different IDs would match (again incorrect, they should be considered different). And I cannot do a DISTINCT ON (id) because then all the rows where ID is NULL would be considered the same (obviously incorrect).

Is there any way to redefine the way PostgreSQL compares rows to determine whether or not they're identical? I guess the default behaviour for DISTINCT ON (name, id) would be something like IF a.name = b.name AND a.id = b.id THEN IDENTICAL ELSE DISTINCT. I'd like to redefine it to something like IF a.id = b.id OR (a.name = b.name AND (a.id IS NULL OR b.id IS NULL)) THEN IDENTICAL ELSE DISTINCT.

It's pretty late and I might have missed something obvious, so other suggestions on how to achieve what I want would also be welcome. Anything to enable me to select distinct rows based on more complex criteria than a simple list of columns. Thanks in advance.

Answer 1

It seems like the main problem is the layout of your database. I don't know the details of your specific application, but having multiple rows and null IDs for the same person is usually a bad idea. If possible you may want to consider creating a separate table for any of the information that requires multiple rows, with persons only containing one row per person and a unique identifier for each row.

But, if you can't do that... I don't think just a distinct is going to solve this problem.

What's the problem with:

select distinct name, id
from persons
where id is not null

Do you have some persons that have a name, but not an ID? Or do you need some specific data from the other rows?

Here's another problem: if there are two rows with the same name and null IDs, and multiple people with the same name and different IDs, how do you know which person the null rows match?

Answer 2

With Window Functions

--
-- First, SELECT those names with NULL national IDs not shadowed by the same
-- name with a national ID.  Each one is a unique person.
--
SELECT name, id
FROM   persons
WHERE  NOT EXISTS (SELECT 1
                     FROM persons p
                    WHERE p.name = persons.name AND p.id IS NOT NULL)
--
-- Second, collapse each national ID into the "first" row with that ID,
-- whatever the name.  Each ID is a unique person.
--
UNION ALL
SELECT name, id
  FROM (SELECT name, id, ROW_NUMBER() OVER (PARTITION BY id)
          FROM persons
         WHERE id IS NOT NULL) d
 WHERE d.row_number = 1;

Without Window Functions

Replace the above UNION with a GROUP BY the first (MIN()) name for each non-NULL id:

...
UNION ALL
  SELECT MIN(name) AS name, id
    FROM persons
   WHERE id IS NOT NULL
GROUP BY id