I'm working with PostgreSQL 9 and I want to find the nearest neighbor inside table RP
for all tuples in RQ
, comparing the dates (t
), but I get this error:
ERROR: subquery in FROM cannot refer to other relations of same query level
using this query:
SELECT *
FROM RQ, (SELECT * FROM RP ORDER BY ABS(RP.t - RQ.t) LIMIT 1) AS RA
RQ.t
in subquery seems to be the problem. How can I avoid this error? How can I get access from subquery to RQ
?
LATERAL
joins allow that and were introduced with Postgres 9.3. Details:
The reason is in the error message. One element of the FROM
list cannot refer to another element of the FROM
list on the same level. It is not visible for a peer on the same level.
You could solve this with a correlated subquery:
SELECT *, (SELECT t FROM rp ORDER BY abs(rp.t - rq.t) LIMIT 1) AS ra
FROM rq
Obviously, you don't care which row from RP
you pick from a set of equally close rows, so I do the same.
However, a subquery expression in the SELECT
list can only return one column. If you want more than one or all columns from the table RP
, use something like this subquery construct:
I assume the existence of a primary key id
in both tables.
SELECT id, t, (ra).*
FROM (
SELECT *, (SELECT rp FROM rp ORDER BY abs(rp.t - rq.t) LIMIT 1) AS ra
FROM rq
) x;
Correlated subqueries are infamous for bad performance. This kind of query - while obviously computing what you want - will suck in particular, because the expression rp.t - rq.t
cannot use an index. Performance will deteriorate drastically with bigger tables.
This rewritten query should be able to utilize an index on RP.t
, which should perform much faster with big tables.
WITH x AS (
SELECT *
,(SELECT t
FROM rp
WHERE rp.t < rq.t
ORDER BY rp.t DESC
LIMIT 1) AS t_pre
,(SELECT t
FROM rp
WHERE rp.t >= rq.t
ORDER BY rp.t
LIMIT 1) AS t_post
FROM rq
)
SELECT id, t
,CASE WHEN (t_post - t) < (t - t_pre)
THEN t_post
ELSE COALESCE(t_pre, t_post) END AS ra
FROM x;
Again, if you want the whole row:
WITH x AS (
SELECT *
,(SELECT rp
FROM rp
WHERE rp.t < rq.t
ORDER BY rp.t DESC
LIMIT 1) AS t_pre
,(SELECT rp
FROM rp
WHERE rp.t >= rq.t
ORDER BY rp.t
LIMIT 1) AS t_post
FROM rq
), y AS (
SELECT id, t
,CASE WHEN ((t_post).t - t) < (t - (t_pre).t)
THEN t_post
ELSE COALESCE(t_pre, t_post) END AS ra
FROM x
)
SELECT id AS rq_id, t AS rq_t, (ra).*
FROM y
ORDER BY 2;
Note the use of parentheses with composite types! No paren is redundant here. More about that in the manual here and here.
Tested with PostgreSQL 9.1. Demo on sqlfiddle.