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

[Patrick Dung <patrick_dkt@xxxxxxxxxxxx>]

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.tanzer@xxxxxxxxx> 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.tanzer@xxxxxxxxxxxxxxxxxxx

(253) 245-3801

Subscribe to the mailing list to

learn more about AGENCY or

follow the discussion.