Re: Use of 'now' constant datatype in view to take advantage of partitioned table

> 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;

I think the problem is that you're using 'now'::date in your first example,
which gets frozen. You can use now() or current_timestamp or current_date
to get dynamic results.

CREATE VIEW test_now AS SELECT current_timestamp as current_ts, now() as
now_function,'now'::timestamp AS now_literal;

(wait a couple of seconds)

SELECT * FROM test_now;

current_ts | now_function |
now_literal
-------------------------------+-------------------------------+----------------------------
2014-08-21 01:25:54.147004-07 | 2014-08-21 01:25:54.147004-07 | 2014-08-21
01:18:22.207073
(1 row)

You'll see that the last column is frozen while the other two stay current.

Cheers,
Ken

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://agency-software.org/demo/client
<https://agency-software.org/demo/client>*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
learn more about AGENCY or
follow the discussion.