Question 89841 in PostgreSQL Question posted in
The official documentation can be found here.

Prioritize one row over another in Postgres – Postgresql

DB-Fiddle

create table items (
  name varchar(15) not null,
  id1 integer,
  id2 integer,
  UNIQUE(id1),
  UNIQUE(id2)
);

insert into items (name, id1, id2) values 
('a', 1, null),
('b', 2, null),
('c', null, 2);

select * from items where id1=2
union
select * from items where id2=2
and id2 not in (select id2 from items where id1=2);

I have a table where there are multiple fields containing the unique id belonging to a given item. In my example, either id1 or id2 contains this value. My goal would be to only rely on id2 if the item cannot be found via id1. So, I would expect to always get back b in my example.

I have managed to get this working via a union, but it seems like a very hacky solution with bad performance. A better solution seems to me is to filter on the client side. What do you think?

Tags:

2

Answers


  1. 0

    We can use ROW_NUMBER here:

    WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY COALESCE(id1, id2) ORDER BY id1) rn
    FROM items
)

SELECT name, id1, id2
FROM cte
WHERE rn = 1;

    Each logical pair of records is described by COALESCE(id1, id2) being the same between them (if a pair exists). Within each pair, we choose the record having the lowest id1 value, which would mean the non null record.

    Login or Signup to reply.
  2. 0

    I would use

    SELECT *
FROM (SELECT * FROM items WHERE id1 = 2
      UNION ALL
      SELECT * FROM items WHERE id1 = 2) AS q
ORDER BY id2 IS NULL
FETCH FIRST 1 ROWS ONLY;

    That relies on the fact that FALSE < TRUE.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search