1

I have a situation like this:

with
my_data as (
  select A.a1,B.b1,B.b2
  from some_table A
  left join lateral (
     select b1, b2 from X where X.id = A.id limit 1
  ) B ON TRUE
)
select * from my_data where b1 is not null
union all
select A.a1,C.b1,C.b2
from my_data A, lateral (
   select b1, b2 from Y where Y.id = A.id limit 1
) C
where A.b1 is null;

and it returns the expected result but I would like to know if there is a better way of writing it.

For example one iteration like:

select A.a1,B.b1,B.b2
from some_data A, lateral (
   select b1, b2 from X where id = A.id limit 1
   -- and if the query on 'X' fails then try:
   select b1, b2 from Y where id = A.id limit 1
) B

any idea?

1 Answer 1

1
select b1, b2 from X where id = A.id limit 1
-- and if the query on 'X' fails then try:
select b1, b2 from Y where id = A.id limit 1

In general, you can implement this with UNION ALL, and then LIMITing the entire query so that the second query is taken only if the first one did not return something:

select b1, b2, 1 as order_me from X where id = A.id
union all
select b1, b2, 2             from Y where id = A.id
order by order_me
limit 1
9
  • if that avoid unnecessary query to the second one, it is great :)
    – Randomize
    Mar 21, 2019 at 9:16
  • is the ordering required?
    – Randomize
    Mar 21, 2019 at 9:30
  • The obvious (and currently used) implementation of UNION ALL is to append the second set of rows to the first one, but the Postgres documentation does not guarantee that this will actually happen in future versions.
    – CL.
    Mar 21, 2019 at 9:41
  • from the documentation I can read: UNION effectively appends the result of query2 to the result of query1 (although there is no guarantee that this is the order in which the rows are actually returned). So the order is needed. Just wondering if the second query gets actually executed even if not needed (I mean if the planner is so smart to catch it)
    – Randomize
    Mar 21, 2019 at 9:51
  • 1
    @CL.: In my experience, the planner is smart enough to detect that. You'll see "never executed" for the second SELECT if the first one returns any rows. See: dba.stackexchange.com/a/193914/3684, dba.stackexchange.com/a/184309/3684 Mar 21, 2019 at 10:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.