Re: EXCEPT call not working - Mailing list pgsql-general

From Tom Lane
Subject Re: EXCEPT call not working
Date
Msg-id 15693.968372341@sss.pgh.pa.us
Whole thread Raw
In response to EXCEPT call not working  (chris mutchler <davron@leibnizcreations.com>)
List pgsql-general
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

pgsql-general by date:

Previous
From: Dave Camarillo
Date:
Subject: Perl Module + postgres 7.0.2
Next
From: Hoosain Madhi
Date:
Subject: type casting varchar to int