I have 6 columns in Postgres table:
A1 character varying(5)[]
A2 character varying(5)[]
A3 int REFERENCES ... -- FK
B1 character varying(5)[]
B2 character varying(5)[]
B3 int REFERENCES ... -- FK
I need a SELECT
where the first row matched is the winner (limit 1)
with matching to A group and B group.
I know Postgres doesn't care about the order of WHERE
clauses and I must prepare an ORDER
clause or find a different approach.
I want to prepare lookup with priority of matching, my importance of WHERE
clauses is as follow:
highest priority: (A1 and B1) OR
. (A1 and B2 OR A2 and B1) OR
. (A1 and B3 OR A3 and B1) OR
. (A2 and B3 OR A3 and B2) OR
lowest priority: (A3 and B3)
The query is matching with 6 values, like:
a1 to A1, a2 to A2, a3 to A3, b1 to B1, b2 to B2, b3 to B3
So A1 and B1 means a1 matched with A1 and b1 matched with B1,
in SQL this part is written as:
("A1" @> ARRAY['19956']::varchar(5)[] AND "B1" @>
ARRAY['27407']::varchar(5)[])
with a1='19956', b1='27407'.
Is it possible to prepare it as single query, despite declarative aspect of SQL? I am considering 5 joins on the same table, but maybe there is an easier way.
Best Answer
This information is crucial:
You do not actually need any sort order for a single result.
ORDER BY
is just one idea how to solve the task. I suggest a completely different approach, probably (much) faster:This is assuming each
SELECT
can only return a single row. Else you get an arbitrary pick or you need to addORDER BY
to individual SELECTs enclosed in parentheses for a deterministic pick. (See linked answers below.)The beauty of it: Postgres stops evaluating as soon as the first row is found. Test with
EXPLAIN ANALYZE
to see "never executed" for trailing SELECTs. So you can use indexes, which might mean orders of magnitude in performance.And you can conveniently return a default row if nothing matches. Just append one more
SELECT
beforeLIMIT 1
.BTW, this is a single query.
Related: