Re: Cannot restore dump when using IS DISTINCT FROM on a HSTORE column

Lele Gaifax <lele(at)metapensiero(dot)it> writes:
> I'm hitting a problem very similar to the one described here[1]

Yeah, nothing's been done about that yet :-(

> where several of those columns are HSTOREs. Trying to restore a dump I get the
> same error: "ERROR: operator does not exist: public.hstore = public.hstore".
> The source and target PG versions are the same, 11.5.
> I followed the link[2] and read the related thread: as it is more that one
> year old, I wonder if there is any news on this, or alternatively if there is
> a recommended workaround:

Probably the simplest solution is to override pg_dump's forcing of the
search_path. It's going to look like this:

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

If you have the dump in a text file you could just edit it and remove
that line (it's only about a dozen lines in, typically). Otherwise
you could delete it with sed or the like, roughly

pg_restore dumpfile | sed s'/^SELECT pg_catalog.set_config('search_path', '', false);//' | psql targetdb

There's been some discussion of providing a simpler way to do this,
but nothing's been done about that either.

Disclaimer: in principle, doing this leaves you open to SQL-injection-like
attacks during the restore, if some malicious user has had access to
either the original source database or your restore target DB. That's
why we put in the search_path restriction. But it doesn't help to be
secure if you can't get your work done ...

regards, tom lane