Postgres: are two columns of TIMESTAMP type with DEFAULT NOW() guaranteed to be equal on row creation (unless updated)?

user_1634905 0
Viewed 399

I have this CREATE TABLE for example with two columns which have DEFAULT NOW() so that when a new row gets created, it automatically gets set to the current TIMESTAMP:

create table threads(threadid uuid PRIMARY KEY default uuid_generate_v4(), reply_to uuid REFERENCES threads(threadid), comment text not null, created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), modified_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW());

And a few example inserts:

insert into threads (threadid, reply_to, comment) values ('40ad95dd-d64e-421a-a4f3-bf7b7017d99a',null,'WOW');
insert into threads (threadid, reply_to, comment) values ('5fe62a5d-e8de-4d3a-935c-a97087168e60','40ad95dd-d64e-421a-a4f3-bf7b7017d99a','MUCH WOW');
insert into threads (threadid, reply_to, comment) values ('f2e41c30-49d5-4420-9aac-00cf98b3d696','5fe62a5d-e8de-4d3a-935c-a97087168e60','WHAT?');
insert into threads (threadid, reply_to, comment) values ('e3f41c30-49d5-4420-9aac-00cf98b3d696','5fe62a5d-e8de-4d3a-935c-a97087168e60','MUCH WOW TO WHAT?');
insert into threads (threadid, reply_to, comment) values ('3c6eccbf-fca4-4c53-9d33-034a32e9b6eb','40ad95dd-d64e-421a-a4f3-bf7b7017d99a','0_0');
insert into threads (threadid, reply_to, comment) values ('ad5d7fc3-fc3a-48d6-ae3c-db8a37ba3464','40ad95dd-d64e-421a-a4f3-bf7b7017d99a','WOW WHAT??');
insert into threads (threadid, reply_to, comment) values ('32ad95dd-d64e-421a-a4f3-bf7b7017d99a',null,'Awesome sauce!');

Is it guaranteed for both the columns to have the exact same timestamp? (obviously as long as the column doesn't get updated to trigger an update of modified_at).

From my simple testing so far, they do seem to always be equal but I would like a bit better confirmation:

select created_at, modified_at from threads ;
          created_at           |          modified_at          
-------------------------------+-------------------------------
 2021-01-30 06:38:07.676708-05 | 2021-01-30 06:38:07.676708-05
 2021-01-30 06:38:07.678601-05 | 2021-01-30 06:38:07.678601-05
 2021-01-30 06:38:07.678977-05 | 2021-01-30 06:38:07.678977-05
 2021-01-30 06:38:07.679341-05 | 2021-01-30 06:38:07.679341-05
 2021-01-30 06:38:07.679547-05 | 2021-01-30 06:38:07.679547-05
 2021-01-30 06:38:07.679734-05 | 2021-01-30 06:38:07.679734-05
 2021-01-30 06:38:36.878027-05 | 2021-01-30 06:38:36.878027-05
postgresql sql
2 Answers

My understanding of the docs are that if the two usages of now() are in the same command, they will return the same time.

Here are the docs - read the last line.

transaction_timestamp() is equivalent to CURRENT_TIMESTAMP, but is named to clearly reflect what it returns. statement_timestamp() returns the start time of the current statement (more specifically, the time of receipt of the latest command message from the client). statement_timestamp() and transaction_timestamp() return the same value during the first command of a transaction, but might differ during subsequent commands. clock_timestamp() returns the actual current time, and therefore its value changes even within a single SQL command. timeofday() is a historical PostgreSQL function. Like clock_timestamp(), it returns the actual current time, but as a formatted text string rather than a timestamp with time zone value. now() is a traditional PostgreSQL equivalent to transaction_timestamp().

From https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

user_31340040
user_86516010
Related