Hi all,
I got a typo in last mail, below is the updated one, with changes in bold fonts.
The method is inspired by Date LastDay - PostgreSQL wiki
Date LastDay - PostgreSQL wiki
Snippets Date LastDay() Works with PostgreSQL Any version Written in SQL
Depends on Nothing by Scott Bailey 'Artacus'
View on wiki.postgresql.org Preview by Yahoo
1. Create a function like:
CREATE OR REPLACE FUNCTION now_function_ts_notz()
RETURNS timestamp without time zone AS
$BODY$
SELECT (NOW())::timestamp without time zone;
$BODY$
LANGUAGE sql IMMUTABLE STRICT
COST 100;
2.
CREATE VIEW v_recent_food AS
SELECT *
FROM food
WHERE food.post_timestamp >= (now_function_ts_notz() - '1
mon'::interval)::date
AND food.post_timestamp <= now_function_ts_notz()::timestamp without time zone
ORDER BY food.post_timestamp DESC
LIMIT 30;
While running 'EXPLAIN ANALYZE select * from v_recent_items'
I found that it skipped the partition table food_2013p.
Is it a valid work around? Or there is other better or elegant way?
Thanks and regards,Patrick
On Thursday, August 21, 2014 4:01 PM, Patrick Dung <patrick_...@yahoo.com.hk>
wrote:
An update, inspired by Date LastDay - PostgreSQL wiki
Date LastDay - PostgreSQL wiki
Snippets Date LastDay() Works with PostgreSQL Any version Written in SQL
Depends on Nothing by Scott Bailey 'Artacus'
View on wiki.postgresql.org Preview by Yahoo
1. Create a function like:
CREATE OR REPLACE FUNCTION now_function_ts_notz()
RETURNS timestamp without time zone AS
$BODY$
SELECT (NOW())::timestamp without time zone;
$BODY$
LANGUAGE sql IMMUTABLE STRICT
COST 100;
2. Create my View like this:
CREATE OR REPLACE VIEW v_recent_items AS
SELECT *
FROM food
WHERE food.post_timestamp >= (now_function_ts_notz() - '1
mon'::interval)::timestamp without time zone AND food.post_timestamp <=
now()::timestamp without time zone
ORDER BY food.post_timestamp DESC
LIMIT 30;
While running 'EXPLAIN ANALYZE select * from v_recent_items'
I found that it skipped the partition table food_2013p.
Is it a valid work around? Or there is other better or elegant way?
Thanks and regards,
Patrick
On Thursday, August 21, 2014 3:21 PM, Patrick Dung <patrick_...@yahoo.com.hk>
wrote:
Resent. As I could not see my mail in the mailing list after about two hours.
On Thursday, August 21, 2014 1:43 PM, Patrick Dung <patrick_...@yahoo.com.hk>
wrote:
Hi Postgresql users,
I have a master table with two partition table (food_2013p, food_2014p).
I found that when I use SELECT + 'now' constant, constraint exclusion works,
(it skipped the 2013 partition).
EXPLAIN ANALYZE
SELECT *
FROM food
WHERE food.post_timestamp >= ('now'::date - interval '1 month')::date AND
food.post_timestamp <= 'now'
ORDER BY food.post_timestamp DESC
LIMIT 30;
But when I put the query inside view, 'now' is converted to the timestamp when
I run the create view.
So the view becomes:
WHERE food.post_timestamp >= ('2014-08-21'::date - '1 mon'::interval)::date
AND food.post_timestamp <= '2014-08-21 13:38:29.642347'::timestamp without time
zone
This is not dynamic.
When I use now(), the query will scan other partition tables. I know this is a
restriction of partition on non-immutable function.
Would it be possible or a feature request to take advantage of the partition
table with query like this?
Thanks and regards,
Patrick
