--
--  Create and connect to the test database
--
CREATE DATABASE test_ce_db;
ALTER DATABASE test_ce_db OWNER TO postgres;

\connect test_ce_db;

--
--  Make a parent table and three child tables partitioned by time using created_at column.
--
CREATE TABLE test_bulletins (
    created_at timestamp with time zone NOT NULL,
    data text NOT NULL
);

CREATE TABLE test_bulletins_20060908 (CONSTRAINT time_partition_limits CHECK (((created_at >= '2006-09-08 00:00:00+00'::timestamp with time zone) AND (created_at < '2006-09-09 00:00:00+00'::timestamp with time zone)))
)
INHERITS (test_bulletins);

CREATE TABLE test_bulletins_20060909 (CONSTRAINT time_partition_limits CHECK (((created_at >= '2006-09-09 00:00:00+00'::timestamp with time zone) AND (created_at < '2006-09-10 00:00:00+00'::timestamp with time zone)))
)
INHERITS (test_bulletins);

CREATE TABLE test_bulletins_20060910 (CONSTRAINT time_partition_limits CHECK (((created_at >= '2006-09-10 00:00:00+00'::timestamp with time zone) AND (created_at < '2006-09-11 00:00:00+00'::timestamp with time zone)))
)
INHERITS (test_bulletins);

--
--  Setup environment for queries. 
--
SET constraint_exclusion = on;
\pset footer off;

--
--  Do test case queries.
--
SELECT 'This query should avoid use of table test_bulletins_20060908, even with OLD CE code' as "Test case 1";
EXPLAIN SELECT * FROM test_bulletins WHERE created_at > '2006-09-09 05:00:00+00'::timestamptz;

SELECT 'This query should avoid use of table test_bulletins_20060908, ONLY with NEW CE code' as "Test case 2";
EXPLAIN SELECT * FROM test_bulletins WHERE created_at > '2006-09-09 05:00:00+00'::timestamptz + '0 days'::interval;

SELECT 'This query uses current timestamp; it should avoid use of table test_bulletins_20060908, ONLY with NEW CE code' as "Test case 3";
EXPLAIN SELECT * FROM test_bulletins WHERE created_at > now() - (now() - '2006-09-09 05:00:00+00'::timestamptz);

SELECT 'This query uses a random value; it should NOT avoid use of table test_bulletins_20060908' as "Test case 4";
EXPLAIN SELECT * FROM test_bulletins WHERE created_at > '2006-09-09 05:00:00+00'::timestamptz - random() * '1 hour'::interval;