CODEWITHSUNDEEP
postgresqlpostgis
Zhafari Irsyad
Zhafari Irsyad

Reputation: 83

How to UNION ALL in PostgreSQL

I want to check list of points inside list of multipolygon. I tried to check all point around 9952 points but i have problem. The problem is the record don't want to show, but if i limit the query, it showing. I want to try to merge queries using UNION ALL.

Query to check all Points

with p as (
    select multipolygon from public.regional
)SELECT * FROM  p,final_results_all_operators
WHERE ST_Contains(ST_GEOMFROMTEXT(p.multipolygon), public.final_results_all_operators.point)

Query with UNION ALL -- Still error

with p as (
    select multipolygon from public.regional
)SELECT * FROM  p,final_results_all_operators
WHERE ST_Contains(ST_GEOMFROMTEXT(p.multipolygon), public.final_results_all_operators.point)
limit 5000
UNION ALL
with c as (
    select multipolygon from public.regional
)SELECT * FROM  c,final_results_all_operators
WHERE ST_Contains(ST_GEOMFROMTEXT(c.multipolygon), public.final_results_all_operators.point)
limit 4952 offset 5000;

Upvotes: 1

Views: 298

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246393

If you have OFFSET, LIMIT or ORDER BY clauses in the queries, you have to use parentheses to not confuse the parser:

(SELECT ...
 LIMIT 500)
UNION
(SELECT ...
 LIMIT 500);

Upvotes: 2

Related Questions