Select / Merge user specific rows with additional fallback rows in PostgreSQL

Question

Setup: Postgresql table with a customer_id and a request_id column (+ additional not relevant data). The rows with customer_id set to NULL work as a fallback/default.

Example what the table looks like:

Goal: I want to select all rows from the table for a given customer (e.g. where customer_id = 2).

For any existent request_id: If there are no entries for the given customer, return the fallback rows (where customer is null).

So the result should look like this:

Any idea how to write the select statement for postgresql? I'm kind of stuck and couldn't really find anything helpful so far. Thanks!

Please use text, not images/links, for text (including code, tables & ERDs). Use an image only for convenience to supplement text and/or for what can't be given in text. Please read & act on minimal reproducible example. — philipxy
– philipxy, Commented Aug 13, 2018 at 17:52

Gordon Linoff · Accepted Answer · 2018-08-13 18:15:28Z

0

This is a strange requirement.

select t.*
from t
where t.customer_id = 2 or
      (t.customer_id is null and
       not exists (select 1 from t t2 where t2.request_id = t.request_id and t2.customer_id = 2)
      );

For performance, I would recommend an index on (request_id, customer_id).

edited Aug 13, 2018 at 18:15

answered Aug 13, 2018 at 16:21

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

lukash Over a year ago

Thank you, this does work, but is very slow (takes 1 to 3 seconds on my local db - compared to 4ms for a select * from t). Any way to optimize it? What is so strange about it? The way we handle default/fallback values? We were trying not to copy all the default values to each customer on create.

Collectives™ on Stack Overflow

Select / Merge user specific rows with additional fallback rows in PostgreSQL

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related