now() default values are all showing same timestamp

postgresql transactions timestamp postgresql-9.1
Ryan Fisch picture Ryan Fisch · Aug 31, 2012 · Viewed 35.1k times · Source

I have created my tables with a column (type: timestamp with timezone) and set its default value to now() (current_timestamp()).

I run a series of inserts in separate statements in a single function and I noticed all the timestamps are equal down to the (ms), is the function value somehow cached and shared for the entire function call or transaction?

Answer

a_horse_with_no_name picture a_horse_with_no_name · Aug 31, 2012

That is expected and documented behaviour:

From the manual:

Since these functions return the start time of the current transaction, their values do not change during the transaction. This is considered a feature: the intent is to allow a single transaction to have a consistent notion of the "current" time, so that multiple modifications within the same transaction bear the same time stamp.

If you want something that changes each time you run a statement, you need to use statement_timestamp() or even clock_timestamp() (again see the description in the manual)

Related questions

Are PostgreSQL functions transactional?

Is a PostgreSQL function such as the following automatically transactional? CREATE OR REPLACE FUNCTION refresh_materialized_view(name) RETURNS integer AS $BODY$ DECLARE _table_name ALIAS FOR $1; _entry materialized_views%ROWTYPE; _result INT; BEGIN EXECUTE 'TRUNCATE TABLE ' || _table_name; …

Read More
How do I do large non-blocking updates in PostgreSQL?

I want to do a large update on a table in PostgreSQL, but I don't need the transactional integrity to be maintained across the entire operation, because I know that the column I'm changing is not going to be written …

Read More
Raw sql transactions with golang prepared statements

I'm having trouble finding some examples that do three of the following things: 1) Allow raw sql transactions in golang. 2) Use prepared statements. 3) Rollback on query failures. I would like to do something like this, but with prepared statements. stmt, stmt_…

Read More