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

Hi Ken,

Thanks for reply.

1.

The problem is that using 'now' in VIEW, the resulting VIEW will hard code the current timestamp.
It is not dynamic.
If I use write the view like this:
 WHERE food.post_timestamp >= ('now'::date - interval '1 month')::timestamp without time zone
 AND food.post_timestamp <= 'now'::timestamp without time zone

The VIEW will be created like this:
  WHERE food.post_timestamp >= ('2014-08-21'::date - '1 mon'::interval) AND food.post_timestamp <= '2014-08-21 17:32:21.787179'::timestamp without time zone

2.

now() is dynamic but it scan all the partitioned tables.

Thanks and regards,
Patrick

On Thursday, August 21, 2014 4:27 PM, Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> wrote:

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/
https://agency-software.org/demo/client

ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing list to
learn more about AGENCY or
follow the discussion.