|Subject:||BUG #11617: issue with dump/restore involving view with hstore data type embedded in where condition|
|Views:||Raw Message | Whole Thread | Download mbox | Resend email|
The following bug has been logged on the website:
Bug reference: 11617
Logged by: Normand Desautels
Email address: support(at)maerix(dot)com
PostgreSQL version: 9.3.4
Operating system: Ubuntu 12.04 LTS
Distributor ID: Ubuntu
Description: Ubuntu 12.04 LTS
PG Version: PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc
(Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bi
To whom it may concern,
in restoring a dump followed by a restore, the restore partially work's but
does not restore a specific view with a hstore component
The error message is:
pg_restore: [archiver (db)] could not execute query: ERROR: operator does
not exist: public.hstore = public.hstore
LINE 30: ...me <> 'fk_formation_id'::text)) AND (h1.old_value IS DISTINC...
HINT: No operator matches the given name and argument type(s). You might
need to add explicit type casts.
The condition in the WHERE clause looks like this:
"WHERE (h1.old_value IS DISTINCT FROM h1.new_value)"
Where the columns old_value and new_value are of the HSTORE datatype, and
That is why we called upon "IS DISTINCT FROM " to manage possible NULL
AS a workaround, we used instead COALESCE with a fake hstore value for NULL
Something along the line of:
where COALESCE( h1.old_value,'"e"=>"1", "f"=>"2"'::hstore) <>
And it resolved the issue when restoring. Everything goes through normally.
Now, either it is a bug similar to the NULLIF issue (see attached
This bug was listed on the 12th of May by Craig Ringer.
Or the use of "IS DISTINCT FROM" clause is irreconcilable with hstore
If that is the case, we will take note of this.
Can you clarify this.
Thanks for your looking it.
|Next Message||Josh Berkus||2014-10-09 21:19:18||Re: schema-only -n option in pg_restore fails|
|Previous Message||Josh Berkus||2014-10-09 19:36:39||schema-only -n option in pg_restore fails|