Hi all
A user has reported a bug where a simple view using hstore does not dump
and restore correctly. I've reproduced the detailed test case they
supplied below.
The original report is by Stack Overflow user 'aidan', here:
http://stackoverflow.com/q/23599926/398670
The error is:
pg_restore: [archiver (db)] could not execute query: ERROR: operator
does not exist: public.hstore = public.hstore
LINE 2: SELECT NULLIF(hstore_test_table.column1, hstore_test_table....
produced by test case:
CREATE DATABASE hstore_test;
\c hstore_test
CREATE EXTENSION hstore WITH SCHEMA public;
CREATE SCHEMA hstore_test_schema;
CREATE TABLE hstore_test_schema.hstore_test_table(
id int,
column1 hstore,
column2 hstore,
PRIMARY KEY( id )
);
CREATE VIEW hstore_test_schema.hstore_test_view AS
SELECT NULLIF(column1, column2) AS comparison FROM
hstore_test_schema.hstore_test_table;
followed by command sequence:
$ pg_dump -U postgres -Fc hstore_test -f test.out
$ dropdb -U postgres hstore_test;
$ createdb -U postgres hstore_test;
$ pg_restore -U postgres -d hstore_test test.out
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 172; 1259 96803 VIEW
hstore_test_view postgres
pg_restore: [archiver (db)] could not execute query: ERROR: operator
does not exist: public.hstore = public.hstore
LINE 2: SELECT NULLIF(hstore_test_table.column1, hstore_test_table....
^
HINT: No operator matches the given name and argument type(s). You
might need to add explicit type casts.
Command was: CREATE VIEW hstore_test_view AS
SELECT NULLIF(hstore_test_table.column1, hstore_test_table.column2) AS
comparison
FROM h...
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services