is there a way to add the same unix timestamp to all the rows in a select statement in PostgreSQL?
The purpose is to have the same value for all the rows – regards how long it runs.
When running the following query – if the query takes long (couple of seconds for millions of rows) then some rows will have a different timestamp.
select id, (extract(epoch from now())) as queryId from books;
Edit:
This is actually works
Your SELECT works as is.
now() is a stable function (volatility STABLE). Meaning, it’s outcome never changes within the same SELECT statement. See:
- How do IMMUTABLE, STABLE and VOLATILE keywords effect behaviour of function?
- Difference between now() and current_timestamp
Why don’t you generate the timestamp first and then select it for all the rows from the books table.
-- Generate the timestamp
WITH query_time AS (SELECT extract(epoch from now()) AS query_timestamp)
-- Use it in your SELECT statement for all the rows
SELECT id, query_timestamp FROM books, query_time
I find this hard to believe.
now()is an alias fortransaction_timestamp()which pins the time at the beginning of the transaction per Current Datetime. A test here withselect a, extract(epoch from now()) from generate_series(1, 10000000) as t(a);that spanned 4.3 seconds showed no difference between the first and last row. You will need to provide more information: 1) Postgres version(community or fork?). 2) OS and version.You are right. I got different results due to a mistake I did.
Alright. At this point you should either: 1) Delete the question or 2) Add information on what the mistake was so other people can learn from it.