Re: pg_dump's restore gives "operator does not exist: public.iprange = public.iprange" but copy paste works - Mailing list pgsql-general

From David G. Johnston
Subject Re: pg_dump's restore gives "operator does not exist: public.iprange = public.iprange" but copy paste works
Date
Msg-id CAKFQuwa8gYMv+97eeQUS4TfagQD_GEjN8MLZFr0RNFj_rsfySA@mail.gmail.com
Whole thread Raw
In response to pg_dump's restore gives "operator does not exist: public.iprange = public.iprange" but copy paste works  ("Christopher Causer" <chy.causer@gmail.com>)
List pgsql-general
On Thu, Jul 8, 2021 at 12:51 PM Christopher Causer <chy.causer@gmail.com> wrote:

```
SELECT pg_catalog.set_config('search_path', '', false);
```

The data types you are using exist in the public schema.  I must assume the associated equality operator also exists in the public schema.  So, when the search_path does not include the public schema that operator cannot be found.  Just like you schema qualified the types as public.iprange you have to also schema qualify the operator.  This is done with the somewhat verbose syntax: operator(public.=) as documented [1].

select last_update from data.subnet_dhcp_options_updates_log where subnet_range operator(public.=) arg_subnet_range;  (might need double quotes...)

Not sure how to deal with implicit operator usage though...in the case of join (on/using) or the IN test...but if the issue is strictly in the written operator usage adding the schema will solve the problem.  Getting the schema back into the search_path is not a viable solution path at present.

David J.



pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: The Curious Case of the Table-Locking UPDATE Query
Next
From: Tom Lane
Date:
Subject: Re: pg_dump's restore gives "operator does not exist: public.iprange = public.iprange" but copy paste works