Thread: 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:
>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