ERROR: subquery in FROM cannot refer to other relations of the same query level

Question

I am working with PostgreSQL 9 and I want to find the closest neighbor inside the RP table for all tuples in RQ by comparing dates ( t ), but I get this error:

ERROR: subquery in FROM cannot refer to other relations of the same query

using this query:

 SELECT * FROM RQ, (SELECT * FROM RP ORDER BY ABS(RP.t - RQ.t) LIMIT 1) AS RA 

RQ.t in the subquery seems to be the problem. How can I avoid this error? How to access from a subquery to RQ ?

Answer 1

Update:

LATERAL joins allow this and were introduced with Postgres 9.3. Details:

• What is the difference between LATERAL and a subquery in PostgreSQL?

---

The reason is in the error message. One element of the FROM list cannot refer to another element of the FROM list at the same level. It is not displayed for the peer at the same level. You can 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 line from RP you choose from a set of equally close lines, so I do the same.

However, a subquery expression in a SELECT list can return only one column. If you want to have more than one or all columns from an RP table, use something like this subquery construct:
I assume the existence of the id primary key 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; 

Related subqueries are notorious for poor performance . This kind of query, in which, obviously, calculates what you want, will suck, in particular, because the expression rp.t - rq.t cannot use the index. Efficiency will drop dramatically with large tables.

---

This rewritten query should be able to use an index on RP.t , which should run much faster with large 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 line:

 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; 

Pay attention to the use of parentheses with compound types ! There is no superfluous. More about this in the manual here and here .

Tested with PostgreSQL 9.1. Demo on sqlfiddle.

Answer 2

Correlation subqueries, without an index, will cross-connect anyway. So another way of expressing a query:

 select rp.*, min(abs(rp.t - rq.t)) from rp cross join rq group by <rp.*> -- <== need to replace with all columns 

There is another method that is a bit more complicated. This requires the use of the total amount.

Here is an idea. Combine all rp and rq values ​​together. Now list them with the closest rp value. That is, create a flag for rp and take the cumulative amount. As a result, all rq values ​​between two rp values ​​have the same rp index.

The closest value for a given rq value is the index rp, the same as the rq value or one more. The calculation of rq_index uses the cumulative sum.

The following query links this:

 with rqi as (select t.*, sum(isRQ) over (order by t) as rq_index from (select rq.t, 0 as isRP, <NULL for each rp column> from rq union all select rq.t, 1 as isRP, rp.* from rp ) t ) t select rp.*, (case when abs(rqprev.t - rp.t) < abs(rqnext.t - rp.t) then abs(rqprev.t - rp.t) else abs(rqnext.t - rp.t) end) as closest_value from (select * from t where isRP = 0 ) rp join (select * from t where isRP = 1 ) rqprev on rp.rp_index = rqprev.rp_index join (select * from t where isRP = 1 ) rqnext on rp.rp_index+1 = rpnext.rq_index 

The advantage of this approach is that there is no cross join and no correlated subqueries.