Postgres Pro
Downloads
Home   >   mailing lists

Re: Use of 'now' constant datatype in view to take advantage of partitioned table - Mailing list pgsql-general

From Ken Tanzer
Subject Re: Use of 'now' constant datatype in view to take advantage of partitioned table
Date
Msg-id CAD3a31UFFF0PfvL5-a873vjBZq10u7STJ063z8xjK+yaJaYq6Q@mail.gmail.com
Whole thread Raw
In response to Re: Use of 'now' constant datatype in view to take advantage of partitioned table  (Patrick Dung <patrick_dkt@yahoo.com.hk>)
Responses Re: Use of 'now' constant datatype in view to take advantage of partitioned table  (Patrick Dung <patrick_dkt@yahoo.com.hk>)
List pgsql-general
Tree view

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
(253) 245-3801

learn more about AGENCY or
follow the discussion.

pgsql-general by date:

Previous
From: Patrick Dung
Date:
Subject: Re: Use of 'now' constant datatype in view to take advantage of partitioned table
Next
From: Patrick Dung
Date:
Subject: Re: Use of 'now' constant datatype in view to take advantage of partitioned table