On Mon, 7 Apr 2003, Laura Moloney wrote:
> The sql that created the original view was:
> CREATE VIEW INCUNRESOLVEDV1 AS
> SELECT DISTINCT TRACKID FROM INCIDENT
> WHERE STATUS = 'o'
> EXCEPT
> SELECT DISTINCT TRACKID FROM INCIDENT_DETAIL
> WHERE LOWER(RESOLVEDYN) = 'y'
> ;
>
> The sql that was generated from pgdump was:
> CREATE VIEW "incunresolvedv1" as
> SELECT DISTINCT incident.trackid FROM incident
> WHERE (incident.status = 'o'::"varchar")
> ORDER BY incident.trackid
> EXCEPT
> SELECT DISTINCT incident_detail.trackid FROM incident_detail
> WHERE (lower((incident_detail.resolvedyn)::text) = 'y'::text)
> ORDER BY incident_detail.trackid;
>
> I found that if I removed the order by clauses, then the sql would execute
> correctly.
>
> I also tried to run the pgdump sql on our 7.2.3 server to see if it had been
> resolved in a newer release, but the same error occurred.
>
> Hope my explanation was clear enough. I can certainly get around it my
> running the original SQL, but I thought you might be interested in it if in fact
> it was a bug.
It seems to have been a bug in pg_dump on 7.1.x that dumped an invalid
query. Current pg_dump dumps the two selects with parentheses around them
which is loadable.