The following bug has been logged online:

Bug reference: 3236
Logged by: Christian Gonzalez
Email address: christian.gonzalez@sigis.com.ve
PostgreSQL version: 8.2.1
Operating system: Red Hat 4.1.1-30
Description: Partitioning has problem with timestamp and timestamptz
data type
Details:

When you use timestamp and timestamptz data type for partitioning
implementation, your postgresql partitioning implementation doesen't work
fine when you make a SELECT using this columns type.

Using Example in PostgreSQL Partitioning page
http://www.postgresql.org/docs/8.2/i...titioning.html

--Create Master Table
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
);

--Create Child Tables
CREATE TABLE measurement_y2004m02 (
CHECK ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2004m03 (
CHECK ( logdate >= DATE '2004-03-01' AND logdate < DATE '2004-04-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2005m11 (
CHECK ( logdate >= DATE '2005-11-01' AND logdate < DATE '2005-12-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2005m12 (
CHECK ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2006m01 (
CHECK ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' )
) INHERITS (measurement);

-- Add two new column (timestamp and timestamptz) ALTER TABLE measurement
ADD COLUMN logdatet timestamp; ALTER TABLE measurement ADD COLUMN logdatett
timestamptz;

-- Test SELECT in column type DATE
SET constraint_exclusion = on;
EXPLAIN SELECT * FROM measurement WHERE logdate = '2006-01-01'
"Result (cost=0.00..50.75 rows=12 width=32)"
" -> Append (cost=0.00..50.75 rows=12 width=32)"
" -> Seq Scan on measurement (cost=0.00..25.38 rows=6 width=32)"
" Filter: (logdate = '2006-01-01'::date)"
" -> Seq Scan on measurement_y2006m01 measurement
(cost=0.00..25.38
rows=6 width=32)"
" Filter: (logdate = '2006-01-01'::date)"

-- Test SELECT in column type timestamp
SET constraint_exclusion = on;
EXPLAIN SELECT * FROM measurement WHERE logdatet = '2006-01-01'
"Result (cost=0.00..152.25 rows=36 width=32)"
" -> Append (cost=0.00..152.25 rows=36 width=32)"
" -> Seq Scan on measurement (cost=0.00..25.38 rows=6 width=32)"
" Filter: (logdatet = '2006-01-01 00:00:00-04'::timestamp with
time zone)"
" -> Seq Scan on measurement_y2004m02 measurement
(cost=0.00..25.38
rows=6 width=32)"
" Filter: (logdatet = '2006-01-01 00:00:00-04'::timestamp with
time zone)"
" -> Seq Scan on measurement_y2004m03 measurement
(cost=0.00..25.38
rows=6 width=32)"
" Filter: (logdatet = '2006-01-01 00:00:00-04'::timestamp with
time zone)"
" -> Seq Scan on measurement_y2005m11 measurement
(cost=0.00..25.38
rows=6 width=32)"
" Filter: (logdatet = '2006-01-01 00:00:00-04'::timestamp with
time zone)"
" -> Seq Scan on measurement_y2005m12 measurement
(cost=0.00..25.38
rows=6 width=32)"
" Filter: (logdatet = '2006-01-01 00:00:00-04'::timestamp with
time zone)"
" -> Seq Scan on measurement_y2006m01 measurement
(cost=0.00..25.38
rows=6 width=32)"
" Filter: (logdatet = '2006-01-01 00:00:00-04'::timestamp with
time zone)"

-- Test SELECT in column type timestamp whit cast to DATE SET
constraint_exclusion = on; EXPLAIN SELECT * FROM measurement WHERE
logdatet::date = '2006-01-01'::date "Result (cost=0.00..170.70 rows=36
width=32)"
" -> Append (cost=0.00..170.70 rows=36 width=32)"
" -> Seq Scan on measurement (cost=0.00..28.45 rows=6 width=32)"
" Filter: ((logdatet)::date = '2006-01-01'::date)"
" -> Seq Scan on measurement_y2004m02 measurement
(cost=0.00..28.45
rows=6 width=32)"
" Filter: ((logdatet)::date = '2006-01-01'::date)"
" -> Seq Scan on measurement_y2004m03 measurement
(cost=0.00..28.45
rows=6 width=32)"
" Filter: ((logdatet)::date = '2006-01-01'::date)"
" -> Seq Scan on measurement_y2005m11 measurement
(cost=0.00..28.45
rows=6 width=32)"
" Filter: ((logdatet)::date = '2006-01-01'::date)"
" -> Seq Scan on measurement_y2005m12 measurement
(cost=0.00..28.45
rows=6 width=32)"
" Filter: ((logdatet)::date = '2006-01-01'::date)"
" -> Seq Scan on measurement_y2006m01 measurement
(cost=0.00..28.45
rows=6 width=32)"
" Filter: ((logdatet)::date = '2006-01-01'::date)"

SET constraint_exclusion = on;
EXPLAIN SELECT * FROM measurement WHERE CAST(logdatet AS DATE) =
CAST('2006-01-01' AS DATE) "Result (cost=0.00..170.70 rows=36 width=32)"
" -> Append (cost=0.00..170.70 rows=36 width=32)"
" -> Seq Scan on measurement (cost=0.00..28.45 rows=6 width=32)"
" Filter: ((logdatet)::date = '2006-01-01'::date)"
" -> Seq Scan on measurement_y2004m02 measurement
(cost=0.00..28.45
rows=6 width=32)"
" Filter: ((logdatet)::date = '2006-01-01'::date)"
" -> Seq Scan on measurement_y2004m03 measurement
(cost=0.00..28.45
rows=6 width=32)"
" Filter: ((logdatet)::date = '2006-01-01'::date)"
" -> Seq Scan on measurement_y2005m11 measurement
(cost=0.00..28.45
rows=6 width=32)"
" Filter: ((logdatet)::date = '2006-01-01'::date)"
" -> Seq Scan on measurement_y2005m12 measurement
(cost=0.00..28.45
rows=6 width=32)"
" Filter: ((logdatet)::date = '2006-01-01'::date)"
" -> Seq Scan on measurement_y2006m01 measurement
(cost=0.00..28.45
rows=6 width=32)"
" Filter: ((logdatet)::date = '2006-01-01'::date)"

We have similar results for timestamptz data type

Why dosen't work?

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq