node-postgres icon indicating copy to clipboard operation
node-postgres copied to clipboard

How to pass array of timestamps with intervals at params?

Open ramirobg94 opened this issue 4 years ago • 1 comments

We are trying to build this query


    const { sessionIds, moderatorIds, payouts, createdAts, publicAts } =
      normalizedData;

    const usersIds = ['uuid0, 'uuid1']
    const createdAts = ['NOW()', 'NOW()']
    const publicAts = [`NOW() + '${user0.delay} seconds'`, `NOW() + '${user1.delay} seconds'`]
    
    return {
      text: `
      INSERT INTO offers (user_id, created_at, public_at) 
      SELECT * 
      FROM UNNEST ($1::uuid[], $2::timestamp[], $3::timestamp[]) 
      RETURNING id, user_id`,
      values: [usersIds, createdAts, publicAts],
    };

but the system returns

invalid input syntax for type timestamp: "NOW() + interval '10 seconds'"

Does anyone knows the right way to build this query

ramirobg94 avatar Jan 09 '22 10:01 ramirobg94

something like

INSERT INTO offers (user_id, created_at, public_at) 
    SELECT user_id, now(), now() + INTERVAL '1 second' * delay
    FROM UNNEST ($1::uuid[], $2::int[]) AS t (user_id, delay)
    RETURNING id, user_id

charmander avatar Jan 09 '22 22:01 charmander