[postgis-users] pg_upgrade error: Operator is not unique

Daniel Baston dbaston at gmail.com
Mon Jan 9 11:26:12 PST 2017

Hi All,

I'm trying to use pg_upgrade to upgrade a large database from Postgres
9.5 to 9.6, both with PostGIS 2.3.1.

The procedure works well except for an error on a single view:

pg_restore: [archiver (db)] could not execute query: ERROR:  operator
is not unique: public.geometry = public.geometry
LINE 53: ...rg" ON (((NOT ("hospital_points"."hospital_point" IS DISTINC...
HINT:  Could not choose a best candidate operator. You might need to
add explicit type casts.
    Command was:
-- For binary upgrade, must preserve pg_type oid
SELECT pg_catalog.binary_upgrade_set_next_pg_type_oid('205493750'::pg_cata...

If I remove the view from the 9.5 database, the entire process works
correctly.  I'm then able to create the view manually in 9.6 after the
restore finishes.

I've verified on both the 9.5 and 9.6 databases that the = operator is
in fact unique for geometry using

SELECT * FROM pg_operator WHERE oprname='=' AND oprleft=oprright AND
oprleft=(SELECT oid FROM pg_type WHERE typname='geometry')

Anyone else run into this before?


More information about the postgis-users mailing list