FAQ
If I create a view like this:
CREATE VIEW v AS
SELECT i FROM a
UNION
SELECT DISTINCT i FROM b

It functions as expected but it causes pg_dump to produce
bad output. "ORDER BY b.i" is added to the view definition.
On restore this causes:
ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of
the result columns

Full setup and steps taken are below.

Regards,

Sam



My setup:

Debian woody.
Linux 2.4.18.
PostgreSQL 7.2.1
./configure --with-maxbackends=64 --with-gnu-ld --enable-odbc
--enable-syslog


What I did:

# cat > test.sql << EOF
SELECT version();
CREATE TABLE foo (i int);
CREATE TABLE bar (i int);

CREATE VIEW this_is_ok
AS SELECT i
FROM foo
UNION
SELECT i FROM bar;

CREATE VIEW this_causes_a_broken_dump
AS SELECT i
FROM foo
UNION
SELECT DISTINCT i FROM bar;

CREATE VIEW this_causes_a_broken_dump_too
AS SELECT i
FROM foo
UNION
SELECT DISTINCT i FROM bar x;
EOF

# createdb test
CREATE DATABASE

# psql test < test.sql
version
---------------------------------------------------------------
PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4
(1 row)

CREATE
CREATE
CREATE
CREATE
CREATE

# pg_dump test
-- snip --

CREATE VIEW "this_is_ok" as SELECT foo.i FROM foo UNION SELECT bar.i
FROM bar;

-- snip --

CREATE VIEW "this_causes_a_broken_dump" as SELECT foo.i FROM foo UNION
SELECT DISTINCT bar.i FROM bar ORDER BY bar.i;

-- snip --

CREATE VIEW "this_causes_a_broken_dump_too" as SELECT foo.i FROM foo
UNION SELECT DISTINCT x.i FROM bar x ORDER BY x.i;

-- snip --

# createdb test2
CREATE DATABASE

# pg_dump test | psql test2
CREATE
CREATE
CREATE
ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of
the result columns
ERROR: Relation "x" does not exist

Search Discussions

  • Tom Lane at Jun 14, 2002 at 11:18 pm

    "Sam O'Connor" <sam@panviva.com> writes:
    If I create a view like this:
    CREATE VIEW v AS
    SELECT i FROM a
    UNION
    SELECT DISTINCT i FROM b
    It functions as expected but it causes pg_dump to produce
    bad output. "ORDER BY b.i" is added to the view definition.
    This appears to be fixed already in current sources --- the problem
    is the lack of parentheses around the arms of the UNION. There's
    a difference between
    SELECT foo UNION SELECT bar ORDER BY baz;
    and
    SELECT foo UNION (SELECT bar ORDER BY baz);
    and the rule dumper was not being careful about it :-(

    I'll see about backpatching this change for 7.2.2.

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-bugs @
categoriespostgresql
postedJun 14, '02 at 2:07a
activeJun 14, '02 at 11:18p
posts2
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Sam O'Connor: 1 post Tom Lane: 1 post

People

Translate

site design / logo © 2022 Grokbase