PostgreSQL need to compare with a set of values, and results shouldnt be empty-CodePudding

I need to compare my field with each value in the array

My queries in code are somehing like this:

1) SELECT agentVersion, lastModified 
FROM am.agents 
WHERE "name"='xxx';
if err != sql.ErrNoRows {
return
}

2) SELECT agentVersion, lastModified 
FROM am.agents 
WHERE "name"='xyz';
if err != sql.ErrNoRows {
return
}

3) SELECT agentVersion, lastModified 
FROM am.agents 
WHERE "name"='zzz';
if err != sql.ErrNoRows {
return
}

Can I make it all in one postgresql query? Thank you!

CodePudding user response：

This presupposes your dataset is reasonably small OR indexed with the number of actual choices being small. If your dataset is huge and there are tens of thousands of possibilities, then this would not scale well at all. If that's the case, I'd recommend a function where you pass an array (or variadic).

So simple solution if you are guaranteed to only have one row of output:

SELECT agentVersion, lastModified
FROM am.agents 
WHERE "name" in ('xxx','xyz','zzz')
order by array_position (array['xxx','xyz','zzz'], name)
limit 1

If it's possible to have more than one row (from each of your queries 1, 2 and 3) then something like this might be necessary.

with cte as (
  SELECT
    agentVersion, lastModified,
    array_position (array['xxx','xyz','zzz'], name) as pos
  FROM am.agents 
  WHERE "name" in ('xxx','xyz','zzz')
)
select agentVersion, lastModified
from cte
where pos = (select min (pos) from cte)

What I hate about these is they fetch data only to discard it. Your solution is actually more efficient.