I have 3 servers running postgres. Two are version 7.1.3 and the other is
7.2.3. I had run pgdump on a database on the 7.1.3 system to create a sql
text file of the structure and data. I then ran that sql text file onto the other
7.1.3 system to create the database there. All SQL ran fine except for one
statement which creates a view.
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;
The error I got when I ran the pgdump sql was:
PostgreSQL said: ERROR: parser: parse error at or near "EXCEPT"
Your query:
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.
Thanks, Laura
Laura Moloney
Theradex
CN5257
Princeton, NJ 08543
(609) 799-7580 (Phone)
(609) 799-4148 (Fax)
LMOLONEY@THERADEX.COM (email)
