Thread: BUG #11884: pg_dump / restore mangles IS DISTINCT FROM expressions

The following bug has been logged on the website:

Bug reference:      11884
Logged by:          Nick Barnes
Email address:      nickbarnes01@gmail.com
PostgreSQL version: 9.3.5
Operating system:   CentOS 6.5
Description:

IS DISTINCT FROM expressions are not dumped/restored correctly if the
comparison operator is outside the search path. e.g.:

psql <<EOF
  CREATE DATABASE test1;
  CREATE DATABASE test2;
  \c test1;
  CREATE SCHEMA s;
  CREATE EXTENSION citext SCHEMA s;
  SET search_path TO public, s;
  CREATE TABLE t (x citext CHECK (x IS DISTINCT FROM ''));
EOF
pg_dump -d test1 | psql -d test2
psql -d test2 -c '\d t'

The check constraint is restored as (x::text IS DISTINCT FROM
''::s.citext::text), i.e. using the <>(text,text) operator instead of
<>(citext,citext).

nickbarnes01@gmail.com writes:
> IS DISTINCT FROM expressions are not dumped/restored correctly if the
> comparison operator is outside the search path. e.g.:

Yeah, this is already under discussion in connection with bug #11617:
http://www.postgresql.org/message-id/flat/20141009200031.25464.53769@wrigleys.postgresql.org

            regards, tom lane