Re: EXCEPT call not working

chris mutchler <davron(at)leibnizcreations(dot)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