brianc / node-postgres

PostgreSQL client for node.js.
https://node-postgres.com
MIT License
12.31k stars 1.23k forks source link

How to pass array of timestamps with intervals at params? #2684

Open ramirobg94 opened 2 years ago

ramirobg94 commented 2 years ago

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

charmander commented 2 years ago

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