Parameterized query with string literal in node-postgres

Question

I'm trying to write a node-postgres query that takes an integer to use in an interval as a parameter:

  const query = {
    text:
      `SELECT
        foo 
        FROM bar 
        WHERE 
          DATE(created_at) >= DATE(NOW()) - INTERVAL '$1 DAYS';`,
    values: [daysAgo]
  }

When I run this, it gives this error message indicating it's not seeing the $1 because it's enclosed in single quotes:

bind message supplies 1 parameters, but prepared statement "" requires 0

Is there a supported way to do this? If not, what is the best work around?

score 6 · Accepted Answer · answered Nov 13 '17 at 07:43

6

I found this reference which shows you can use the convention of '1 DAY' * X.

I was able to get this working thus:

   (async () => {
       const { rows } = await pgpool.query(`
          SELECT id, name FROM users 
           WHERE DATE(created) <= DATE(NOW()) - INTERVAL '1 DAY' * $1;`,
           [req.params.daysAgo]);

answered Nov 13 '17 at 07:43

rotarydial

2,181
2
23
27

@alexroussos, if you need to see the full call with catch(), etc. Let me know and I'll edit my answer. – rotarydial Nov 13 '17 at 14:15
1

That's perfect. Thanks – alexroussos Nov 13 '17 at 16:46

Parameterized query with string literal in node-postgres

1 Answers1