PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How does `now()` work as part of an SQL insert statement?

I have a SQL statement like this:

insert into table1 (col1, col2, col3, col4)
select
    value1,
    value2,
    now(),
    now()
from
    othertable inner join anotherothertable on somecolumn = someothercolumn;

The select component of this query will return many hundreds of thousands of rows.

How will the call to now() behave?

  • Will it return the same value for all rows inserts?
  • Will it return the same value in the same insert, given that it appears to be called twice per single row insertion?

I want to perform this insert such that now() is called once, and that value is fixed and used when inserting all the rows and data. I am not sure if this query will behave this way or not.

Since there is no way to do something like current_timestamp = now() in SQL and use current_timestamp as an argument in place of the two calls to now(), I am not sure how to do this.

Tags:

4

Answers


  1. 0

    You can Store the value in new variable and save it like this :

    Declare @tempTime DATETIME = GETDATE();
insert into table1 (col1, col2, col3, col4) 
select 
  value1, 
  value2, 
  @tempTime, 
  @tempTime 
from 
  othertable 
  inner join anotherothertable on somecolumn = someothercolumn;
    Login or Signup to reply.
  2. 0

    Declaring and using a variable is best option IMHO.

    create table table1 (col1 int, col2 int, col3 timestamp, col4 timestamp);

DO $$
 DECLARE _now timestamp;
 
BEGIN
  select now() into _now;
  
insert into table1 (col1, col2, col3, col4)
select
    x.v,
    x.v*10,
    _now,
    _now
from
    generate_series(1, 10) x(v);
END $$;

select * from table1;
    col1 col2 col3 col4
    1 10 2023-12-20 11:35:43.987329 2023-12-20 11:35:43.987329
    2 20 2023-12-20 11:35:43.987329 2023-12-20 11:35:43.987329
    3 30 2023-12-20 11:35:43.987329 2023-12-20 11:35:43.987329
    4 40 2023-12-20 11:35:43.987329 2023-12-20 11:35:43.987329
    5 50 2023-12-20 11:35:43.987329 2023-12-20 11:35:43.987329
    6 60 2023-12-20 11:35:43.987329 2023-12-20 11:35:43.987329
    7 70 2023-12-20 11:35:43.987329 2023-12-20 11:35:43.987329
    8 80 2023-12-20 11:35:43.987329 2023-12-20 11:35:43.987329
    9 90 2023-12-20 11:35:43.987329 2023-12-20 11:35:43.987329
    10 100 2023-12-20 11:35:43.987329 2023-12-20 11:35:43.987329

    DBFiddle demo

    Login or Signup to reply.
  3. 0

    This is thoroughly documented at Date/Time Functions and Operators but, essentially, your code is fine as is if your intention is to have the same time in every row. It’ll return the same value for each individual statement, or even the whole transaction.

    You can do a quick test:

    START TRANSACTION;

SELECT now();
-- Wait a few seconds
SELECT now();

ROLLBACK;

    docker=# START TRANSACTION;
START TRANSACTION
docker=*# SELECT now();
              now              
-------------------------------
 2023-12-20 11:55:11.761564+00
(1 row)

docker=*# SELECT now();
              now              
-------------------------------
 2023-12-20 11:55:11.761564+00
(1 row)

docker=*# ROLLBACK;
ROLLBACK
docker=#
    Login or Signup to reply.
  4. 0

    The calls to function ‘now’ in your statement return the same time for all rows in
    your statement. The time returned is the time the current transaction was started,
    which is at the time of the current statement or an earlier statement in the
    transaction.

    The documentation for that is in the Postgres manual, 9.9.5. Current Date/Time.

    Here is a summary:

    function standard? which time?
    CURRENT_DATE yes transaction
    CURRENT_TIME yes transaction
    CURRENT_TIMESTAMP yes transaction
    CURRENT_TIME(precision) yes transaction
    CURRENT_TIMESTAMP(precision) yes transaction
    LOCALTIME yes transaction
    LOCALTIMESTAMP yes transaction
    LOCALTIME(precision) yes transaction
    LOCALTIMESTAMP(precision) yes transaction
    transaction_timestamp() no transaction
    statement_timestamp() no statement
    clock_timestamp no clock time
    timeofday no clock time
    now() no transaction

    So, you don’t need to put the time into a ‘variable’. If for some reason, you still
    wanted to do that, you can isolate the time into a ‘variable’ with the help of a
    common table expression (WITH query), without leaving SQL:

    WITH my_time(t) AS ( VALUES (now()) )
insert into table1 (col1, col2, col3, col4)
select
    value1,
    value2,
    t,
    t
from my_time,
     othertable
join anotherothertable on somecolumn = someothercolumn
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search