PostgreSQL – Is It Possible to Schema-Qualify an Operator?

operatorpostgispostgresqlschema

Let's say I create an extension, like PostGIS, in the schema postgis,

CREATE EXTENSION postgis WITH SCHEMA postgis;

Is it possible to fully qualify an operator like && in the below example,

SELECT box2d && ST_MakePoint(5,5)
FROM postgis.box2d(postgis.ST_MakeEnvelope(0,0,10,10));

But "postgis.&&" and even "postgis".&& and "postgis"."&&" is not working for me. The docs on "Operator Type Resolution" seem to hint at being possible,

If a qualified operator name was given, only operators in the specified schema are considered.

Put another way, can I use an operator that is "not visible".

SELECT pg_type_is_visible(
  'postgis.&&(postgis.geometry,postgis.geometry)'::regoperator
);

Clearly, I can add postgis to my search_path but I'm looking to leave that unmodified and in ever other context we can in fact qualify everything.

Best Answer

Be aware that the OPERATOR construct (like OPERATOR(postgis.&&)) is not 100 % identical to the operator itself. Operators lose their implicit precedence and fall back to default operator precedence, which can have sneaky side effects. Consider:

SELECT 3 + 3 * 2                        -- 9
     , 3 + 3 OPERATOR(pg_catalog.*) 2;  -- 12

There is no effect for OPERATOR(postgis.&&) as && has default operator precedence to begin with.

Related:

Another limitation: the OPERATOR construct does not work for SQL syntax elements like AND, OR, BETWEEN etc., which are also sometimes called "operators" (and have their own operator precedence). Only for operators listed in the system catalog pg_operator.

Related Question