EXCEPT call not working
I have a perl script that interacts with my postgresql server through the
DBI module. When, I send my query to the server it will work fine with
the following:
$sth = $dbh->prepare("
SELECT p.ids_name, m.start_time, m.end_time
FROM mail_schedule m, personnel p
WHERE p.ids_int = m.ids_int AND
m.dow = $weekday
UNION
SELECT p.ids_name, c.on_start, c.on_end
FROM mail_changes c, personnel p
WHERE p.ids_int = c.ids_int
");
But as soon as I place the EXCEPT call in there it stops working and
doesn't return any values:
$sth = $dbh->prepare("
SELECT p.ids_name, m.start_time, m.end_time
FROM mail_schedule m, personnel p
WHERE p.ids_int = m.ids_int AND
m.dow = $weekday
UNION
SELECT p.ids_name, c.on_start, c.on_end
FROM mail_changes c, personnel p
WHERE p.ids_int = c.ids_int
EXCEPT
SELECT p.ids_name, c.on_start, c.on_end
FROM mail_changes c, personnel p
WHERE p.ids_int = c.ids_int
AND c.on_start = 0
AND c.on_end = 0
");
Does anyone have any suggestions as to why my EXCEPT call isn't working?
If you see some drastic or subtle syntax error let me know. I have never
used EXCEPT before, and am not having much luck. thanks.
Chris Mutchler
davron@leibnizcreations.com
chris mutchler <davron@leibnizcreations.com> writes:
But as soon as I place the EXCEPT call in there it stops working and
doesn't return any values:
Are you sure the query *should* be returning any values? It's not
obvious from your example whether the EXCEPT might not exclude all
the values from the UNION.
One thing to look at is that the current implementation of union
has some problems if the column datatypes aren't exactly alike in
the union'd selects. For example if you have
SELECT p.ids_name, m.start_time, m.end_time
FROM mail_schedule m, personnel p
WHERE p.ids_int = m.ids_int AND
m.dow = $weekday
UNION
SELECT p.ids_name, c.on_start, c.on_end
FROM mail_changes c, personnel p
WHERE p.ids_int = c.ids_int
and, say, m.start_time is DATE while c.on_start is TIMESTAMP, it'll try
to work but probably have problems. Explicitly casting the columns to
the same datatype is the best workaround at the moment.
I'm not sure offhand whether the same is true for except and intersect,
but could be...
regards, tom lane
EXCEPT doesn't work well in PostGres. Try replacing EXCEPT with NOT IN (...)
At 06:00 PM 9/7/00, chris mutchler wrote:
Show quoted text
I have a perl script that interacts with my postgresql server through the
DBI module. When, I send my query to the server it will work fine with
the following:$sth = $dbh->prepare("
SELECT p.ids_name, m.start_time, m.end_time
FROM mail_schedule m, personnel p
WHERE p.ids_int = m.ids_int AND
m.dow = $weekday
UNION
SELECT p.ids_name, c.on_start, c.on_end
FROM mail_changes c, personnel p
WHERE p.ids_int = c.ids_int
");But as soon as I place the EXCEPT call in there it stops working and
doesn't return any values:$sth = $dbh->prepare("
SELECT p.ids_name, m.start_time, m.end_time
FROM mail_schedule m, personnel p
WHERE p.ids_int = m.ids_int AND
m.dow = $weekday
UNION
SELECT p.ids_name, c.on_start, c.on_end
FROM mail_changes c, personnel p
WHERE p.ids_int = c.ids_int
EXCEPT
SELECT p.ids_name, c.on_start, c.on_end
FROM mail_changes c, personnel p
WHERE p.ids_int = c.ids_int
AND c.on_start = 0
AND c.on_end = 0
");Does anyone have any suggestions as to why my EXCEPT call isn't working?
If you see some drastic or subtle syntax error let me know. I have never
used EXCEPT before, and am not having much luck. thanks.Chris Mutchler
davron@leibnizcreations.com