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(at)postgresql(dot)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