Postgres: ERROR: operator does not exist: point <@> point

Question

Using PostgreSQL 11 on Windows.

Both cube and earthdistance installed and verified by pg_available_extensions.

Restarted PostgreSQL.

[42883] ERROR: operator does not exist: point <@> point.

Setup:

CREATE EXTENSION IF NOT EXISTS cube SCHEMA temp;
CREATE EXTENSION IF NOT EXISTS earthdistance SCHEMA temp;

Tried the following code from StackOverflow

create table temp.lat_lon (
 city varchar(50) primary key,
 lat float8 not null,
 lon float8 not null
);
insert into temp.lat_lon values
('London, GB', 51.67234320, 0.14787970),
('New York, NY', 40.91524130, -73.7002720);
select
 (
 (select point(lon,lat) from temp.lat_lon where city = 'London, GB') <@>
 (select point(lon,lat) from temp.lat_lon where city = 'New York, NY')
 ) as distance_miles;

Throws:

[42883] ERROR: operator does not exist: point <@> point.

Extensions installed and Postgres was restarted.

select * from pg_available_extensions where name IN ('cube', 'earthdistance');

cube 1.4 data type for multidimensional cubes
earthdistance 1.1 calculate great-circle distances on the surface of the Earth

Is this because of PostgreSQL v11 on Windows? Yes, Lat/Long in correct order (long is first).

Note: Table F.6 Point-based Earthdistance Operators

Update in regards to: schema / search path:

SELECT extname, extnamespace::regnamespace FROM pg_extension
WHERE extname IN ('cube', 'earthdistance');
cube temp
earthdistance temp

SHOW search_path;
temp

Note: I CREATE EXTENSION hstore SCHMEA temp; and can use hstore and its operators. So doesn't seem to be all extensions.

• Evan Carroll: Just wanted to turn you onto PostGIS if you never heard of it. It does a lot more, and the indexing is better than Earthdistance.
• thames: Thanks @EvanCarroll PostGIS is a little too much for this particular usage. Only sparingly need to use earthdistance <@>. If the project evolves to needing more geo, PostGIS is the next option.

Answer 1

Possible explanation: you installed the extension in a schema that's missing from your current search_path.

Diagnose with:

SELECT extname, extnamespace::regnamespace FROM pg_extension
WHERE extname IN ('cube', 'earthdistance');
SHOW search_path;

Is the schema of the extensions in your current search_path? If not, there is your explanation. Either install to a different schema or adapt your search_path.

And:

SELECT oprnamespace::regnamespace, oprleft::regtype, oprname, oprright::regtype
FROM pg_operator
WHERE oprname = '<@>';

Related:

• Is it recommended to install extensions into pg_catalog schema?
• How does the search_path influence identifier resolution and the "current schema"

• thames: Unfortunately they are in the same schema and search path. There is only one schema and that schema is in the search path where the extension was installed. I wish that was the answer!
• Erwin Brandstetter: Hrmpf. So anything suspicious from this query maybe? SELECT oprnamespace::regnamespace, oprleft::regtype, oprname, oprright::regtype FROM pg_operator WHERE oprname = '<@>';
• thames: Yeah that comes back with temp point <@> point so the operator is loaded/setup. No clue why it's not working.
• thames: Ok. This is weird. Now it's working. I didn't try it before I tried your pg_operator query, but I did after and now it seems to work. Not sure why now. Pg wasn't restarted. I did drop/create everything from scratch, but when I tried it after drop/create I received the same error as before. The only difference is that I left the computer for about 45 min. Not sure how that would affect anything especially when I restarted Pg before and it still didn't work. Does pg_operator refresh any system metadata?
• Erwin Brandstetter: pg_operator is a system catalog (a plain table). It does not "do" anything. The most obvious explanation would be that the failing session ran with a different search_path for some reason. Maybe a setting per role? Details in the 2nd link above.