2

How to select from a table with an equality predicate on point column?
To be clear: it's a native Postgres point data type, not geometry(point) from PostGIS.

select a.* from indsolv.address As a where a.location = '93.2321, 21.0321';

Getting this error for the above query:

ERROR:  operator does not exist: point = unknown
LINE 1: ...ct a.* from indsolv.address As a where a.location = '93.2321...
                                                         ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
SQL state: 42883

Also tried:

select a.* from indsolv.address As a where a.location = point('93.2321,21.0321');

Still getting an error.

3
  • Do not cross-post: gis.stackexchange.com/questions/339988/…
    – Philᵀᴹ
    Oct 28, 2019 at 12:39
  • 1
    Please clarify (and then decide on which board you want to post) if this is about the native PostgreSQL POINT type, or the PostGIS GEOMETRY(POINT) type!
    – geozelot
    Oct 28, 2019 at 12:53
  • 1
    Please edit your question (by clicking on the edit link below it) and add the complete create table statement for the table in question.
    – user1822
    Oct 28, 2019 at 13:18

1 Answer 1

3

Try as it might, operator type resolution will find no appropriate operator and give up with the error message you reported.

ERROR:  operator does not exist: point = unknown

Adding an explicit type cast (or the point() function achieving the same) to the right argument like you did in your added second attempt will only succeed in making the error message a bit more revealing:

ERROR:  operator does not exist: point = point

The subtle difference may serve as hint: the problem is not a missing cast (though being explicit about data types is never a bad idea). The problem is that there is, in fact, no equality operator = defined for the data type point. The manual:

Note that the “same as” operator, ~=, represents the usual notion of equality for the point, box, polygon, and circle types. Some of these types also have an = operator, but = compares for equal areas only. The other scalar comparison operators (<= and so on) likewise compare areas for these types.

For the data type point, there is no = at all. See for yourself (tested in Postgres 12):

SELECT *            -- no rows
FROM   pg_operator
WHERE  oprleft = 'point'::regtype
AND    oprname = '=';

The solution is to use the appropriate "same as" operator ~= instead. Works with or without explicit type cast, with or without enclosing parentheses in the string literal, and with the function notation you tested as well - and in this case with either type of string literal as well as with two separate parameters as there are multiple overloaded versions of the point() function:

SELECT a.* FROM indsolv.address a
WHERE a.location ~= point '(93.2321, 21.0321)';

... WHERE a.location ~= point '93.2321, 21.0321';
... WHERE a.location ~= '93.2321, 21.0321'::point;
... WHERE a.location ~= '(93.2321, 21.0321)'::point;
... WHERE a.location ~= cast('93.2321, 21.0321' AS point);
... WHERE a.location ~= cast('(93.2321, 21.0321)' AS point);

... WHERE a.location ~= '(93.2321, 21.0321)';
... WHERE a.location ~= '93.2321, 21.0321';

... WHERE a.location ~= point('93.2321, 21.0321)';
... WHERE a.location ~= point('(93.2321, 21.0321)');
... WHERE a.location ~= point('93.2321', '21.0321');

Related:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.