postgres - ERROR: operator does not exist

Question

Again, I have a function that works fine locally, but moving it online yields a big fat error... Taking a cue from a response in which someone had pointed out the number of arguments I was passing wasn't accurate, I double-checked in this situation to be certain that I am passing 5 arguments to the function itself...

Query failed: ERROR: operator does not exist: point <@> point HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts.

The query is this:

BEGIN; SELECT zip_proximity_sum('zc',                                                                                                                                                                  
    (SELECT g.lat FROM geocoded g                                                                                                                                                                              
    LEFT JOIN masterfile m ON g.recordid = m.id                                                                                                                                                                
    WHERE m.zip = '10050' ORDER BY m.id LIMIT 1),                                                                                                                                                             
    (SELECT g.lon FROM geocoded g                                                                                                                                                                              
    LEFT JOIN masterfile m ON g.recordid = m.id                                                                                                                                                                
    WHERE m.zip = '10050' ORDER BY m.id LIMIT 1),                                                                                                                                                             
    (SELECT m.zip FROM geocoded g                                                                                                                                                                              
    LEFT JOIN masterfile m ON g.recordid = m.id                                                                                                                                                                
    WHERE m.zip = '10050' ORDER BY m.id LIMIT 1)                                                                                                                                                              
    ,10);

The PG function is this:

CREATE OR REPLACE FUNCTION zip_proximity_sum(refcursor, numeric, numeric, character, numeric)
  RETURNS refcursor AS
$BODY$ 
    BEGIN 

        OPEN $1 FOR 
            SELECT r.zip, point($2,$3) <@> point(g.lat, g.lon) AS distance
            FROM
            geocoded g LEFT JOIN masterfile r ON g.recordid = r.id 
            WHERE (geo_distance( point($2,$3),point(g.lat,g.lon)) < $5)
            ORDER BY r.zip, distance;
        RETURN $1; 
    END; 
    $BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;

I'm sorry if this sounds a bit harsh, but do you actually read the error message??? — Frank Bollack
– Frank Bollack, Commented Apr 19, 2010 at 7:34
Did you ever figure out what was going on? Happening to me. Both cube and earthdistance installed and verified by pg_available_extensions. Restarted PostgreSQL. [42883] ERROR: operator does not exist: point <@> point. Maybe an issue with PostgreSQL v11 on Windows? — thames
– thames, Commented Mar 6, 2019 at 0:13

Steve Tauber · Accepted Answer · 2014-08-05 13:47:38Z

13

Here are the exact commands:

create extension cube;
create extension earthdistance;
select (point(-0.1277,51.5073) <@> point(-74.006,40.7144)) as distance;

     distance     
------------------
 3461.10547602474
(1 row)

Note that points are created with LONGITUDE FIRST. Per the documentation:

Points are taken as (longitude, latitude) and not vice versa because longitude is closer to the intuitive idea of x-axis and latitude to y-axis.

Which is terrible design... but that's the way it is.

answered Aug 5, 2014 at 13:47

Steve Tauber

10.3k5 gold badges45 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

thames Over a year ago

This doesn't work. Both cube and earthdistance installed and verified by pg_available_extensions. Restarted PostgreSQL. [42883] ERROR: operator does not exist: point <@> point. Maybe an issue with PostgreSQL v11 on Windows? Yes Long/Lat in correct order.

Kevin Potgieter Over a year ago

Its not a terrible design, its the default. Postgis by default is Long Lat. We usually talk about x,y, which in this case is Long Lat, respectively.

marcinn · Accepted Answer · 2020-03-18 19:47:11Z

4

The <@> operator is provided by earthdistance extension. You need to call create extension earthdistance; on production database.

More info about Earthdistance: http://www.postgresql.org/docs/current/static/earthdistance.html
Checking available extensions: http://www.postgresql.org/docs/current/static/view-pg-available-extensions.html

edited Mar 18, 2020 at 19:47

user330315

answered Feb 13, 2014 at 14:21

marcinn

5464 silver badges13 bronze badges

1 Comment

thames Over a year ago

Yes but it still throws error. Both cube and earthdistance installed and verified by pg_available_extensions. Restarted PostgreSQL. [42883] ERROR: operator does not exist: point <@> point. Maybe an issue with PostgreSQL v11 on Windows?

Java Dev · Accepted Answer · 2021-01-13 06:19:12Z

4

You need to create extensions under pg_catalog schema. So that those can be available globally. Please execute below commands:

drop extension if exists earthdistance;
drop extension if exists cube;
create extension cube schema pg_catalog;
create extension earthdistance schema pg_catalog;

answered Jan 13, 2021 at 6:19

Java Dev

412 bronze badges

2 Comments

stateMachine Over a year ago

The question is a little bit old (10 years) so, I don't know if your answer is still relevant.

green.maru Over a year ago

This helped me out, thank you for pointing out the schema parameter!

MaxTryk · Accepted Answer · 2020-03-18 21:02:55Z

1

You need to qualify <@> operator like this:

OPERATOR(schema.<@>)

if the schema in which the extension is created is not listed in search_path:

SHOW search_path;

https://www.postgresql.org/docs/current/ddl-schemas.html

edited Mar 18, 2020 at 21:02

answered Mar 18, 2020 at 19:44

MaxTryk

714 bronze badges

Comments

rofrol · Accepted Answer · 2025-03-26 19:54:02Z

Here are the steps to solve this problem

Run below command to check the extensions (cube & earthdistance)

SELECT * FROM pg_available_extensions ORDER BY "name"

Run the below command if still both extensions (cube & earthdistance) showing in the list:

CREATE EXTENSION cube;
CREATE EXTENSION earthdistance;

create table and insert values

Create Location Table

CREATE TABLE location (
    id serial PRIMARY KEY,
    name varchar(50) NOT NULL,
    longitude double precision NOT NULL,
    latitude double precision NOT NULL
);

Insert Us Some Data (Sites around #CHA)

INSERT INTO location(name, latitude, longitude)
VALUES ('SupplyHog HQ', 35.0472780, -85.3071590)
,('Chickamauga Dam', 35.0975557,-85.2197027)
,('Five Points Mtn Biking', 34.851249, -85.423983)
,('Harrison Bay State Park', 35.179631, -85.114359)
,('Mojo Burrito', 35.0094040,-85.3275640)
,('Rock Creek', 35.0556150,-85.2803290);

Run select query you will get the result

When creating points, it is point(long, lat)

SELECT *, point(-85.3078294, 35.0609500) <@> point(longitude, latitude)::point as distance 
FROM location
WHERE (point(-85.3078294, 35.0609500) <@> point(longitude, latitude)) < 10
ORDER BY distance;

Frank Bollack · Accepted Answer · 2010-04-19 08:36:32Z

0

The point of error is the select statement in your stored procedure:

SELECT r.zip, point($2,$3) <@> point(g.lat, g.lon) AS distance
                            ^

At the marked position an operator is expected, but your operator is either not defined or has other argumet types. Postgres itself only knows of the <@ and @> containment operators. Look here for their explanation.

Can you please elaborate on what you are trying to achieve.

answered Apr 19, 2010 at 8:36

Frank Bollack

25.3k5 gold badges51 silver badges58 bronze badges

1 Comment

thames Over a year ago

Unhelpful answer. Both cube and earthdistance installed and verified by pg_available_extensions. Restarted PostgreSQL. [42883] ERROR: operator does not exist: point <@> point. Maybe an issue with PostgreSQL v11 on Windows? Yes Lat/Long in correct order (long is first).

Collectives™ on Stack Overflow

postgres - ERROR: operator does not exist

6 Answers 6

2 Comments

1 Comment

2 Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

1 Comment

2 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related