Re: Select Union

Randall Skelton wrote:

> I have a number of tables with the general structure:
>
>   Column   |           Type           | Modifiers
> -----------+--------------------------+-----------
>  timestamp | timestamp with time zone |
>  value     | double precision         |
> Indexes: tbl__timestamp
>
> and I would like to find the union of the timestamps.  Something like:
>
> select timestamp from cal_quat_1 WHERE timestamp BETWEEN '2004-02-01
> 00:03:30' AND '2004-02-01 00:04:00' UNION select timestamp from
> cal_quat_2 WHERE timestamp BETWEEN '2004-02-01 00:03:30' AND
> '2004-02-01 00:04:00' UNION select timestamp from cal_quat_4 WHERE
> timestamp BETWEEN '2004-02-01 00:03:30' AND '2004-02-01 00:04:00'
> UNION select timestamp from cal_quat_4 WHERE timestamp BETWEEN
> '2004-02-01 00:03:30' AND '2004-02-01 00:04:00' UNION select timestamp
> from cal_ccd_temp WHERE timestamp BETWEEN '2004-02-01 00:03:30' AND
> '2004-02-01 00:04:00';
>
> Is there a less shorter, less redundant way of writing this?
>
> Cheers,
> Randall
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)


SELECT t1.timestamp FROM (
    SELECT timestamp FROM cal_quat_1 UNION
    SELECT timestamp FROM cal_quat_2 UNION
    SELECT timestamp FROM cal_quat_3 UNION
    SELECT timestamp FROM cal_quat_4 UNION
    SELECT timestamp FROM cal_ccd_temp
) t1 WHERE
    t1.timestamp BETWEEN '2004-02-01 00:03:30' AND '2004-02-01 00:04:00';

is technically shorter but I have no idea how well it will compare
performance wise with what you have got. If this runs a lot slower then
compare the output from explain analyze of the two queries.


Nick