I have a simple table with two columns:

I have to build a query which returns the corresponding value if the Product Id exists in the table, as in if P123 --> 2. Select Value from Product where ProductId='P123'

If we do not have the corresponding value for the Product Id, then return the value for the Global Product Id, as in for P456 we should return --> 1

Can you please let me know how we can perform the same in a single postgres query?

CodePudding user response：

You could filter corresponding and default products and make use of ORDER and LIMIT to select the corresponding if it exists. Something like that:

SELECT 
  value 
FROM 
  product
WHERE
  ProductId='P123' OR ProductId='Global'
ORDER BY
  CASE WHEN ProductId='Global' THEN 1 ELSE 0 END ASC
LIMIT
  1

The CASE clause is needed to position product with ID 'Global' last.

CodePudding user response：

Use coalesce on scalar subqueries.

select coalesce
(
 (select value from t where productid = 'P123'), 
 (select value from t where productid = 'Global')
) as value;

CodePudding user response：

You can use a UNION

with data as (
  select value
  from product
  where productid = 'P123'
)
select *
from data
union all
select value
from product
where productid = 'Global'
  and not exists (select * from data);

The second part of the union will only be executed if the first one didn't return any results.