syntax error at or near "Z" when trying to create materialized view

Question

I have the following query:

CREATE MATERIALIZED VIEW tk AS
SELECT * 
FROM base
WHERE ST_Contains(geo, POLYGON Z ((...some values ...)))

But I'm getting error:

syntax error at or near "Z"

the geo field is geometry field type

What is the problem and how to solve it ?

Answer 1

Your query is missing the single quotes:

Example:

CREATE TABLE base (geo geometry);
INSERT INTO base VALUES ('POLYGONZ((1 0 0, 0 5 0, 5 5 0, 5 0 0, 1 0 0))');

CREATE MATERIALIZED VIEW tk AS
SELECT * FROM base
WHERE 
  ST_Contains (geo,'POLYGONZ((2.51 3.33 1,2.07 2.24 1,3.28 2.26 1,2.51 3.33 1))');

SELECT ST_AsText(geo) FROM tk;

                  st_astext                  
---------------------------------------------
 POLYGON Z ((1 0 0,0 5 0,5 5 0,5 0 0,1 0 0))
(1 Zeile)

Note: The documentation of ST_Contains does not mention anything about also computing the Z dimension in the function. Therefore we can assume that it is simply ignored and the query just takes X and Y into account.