SQL error 42601 when running query in postgreSQL

Question

I am trying to run following query using PHP PDO library against a pgSQL Database table

SELECT COUNT(*) AS overall, COUNT(IF(username = ?, TRUE, NULL)) AS unames FROM login_attempt WHERE last_checked > NOW() - INTERVAL ? MICROSECOND

but getting following error

Error: SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "$2" LINE 1: ...gin_attempt WHERE last_checked > NOW() - INTERVAL $2 MICROSE... ^

here is my full code of function

private function isQueueSizeExceeded()
{

    $sql = "SELECT COUNT(*) AS overall, COUNT(IF(username = :uname, TRUE, NULL)) AS unames FROM login_attempt WHERE last_checked > NOW() - INTERVAL :itime MICROSECOND";
    $stmt = $this->pdo->prepare($sql);

    $itime= 5000 * 1000;
    $stmt->bindParam(':uname', $this->username);
    $stmt->bindParam(':itime', $itime);
    $stmt->execute();

    $count = $stmt->fetch(PDO::FETCH_OBJ);
    if (!$count) {
        throw new Exception("Failed to query queue size", 500);
    }

    return ($count->overall >= self::MAX_OVERALL || $count->unames >= self::MAX_PER_USER);
}

this code is expected to run using MySQL but i am trying to run using postgreSQL

Postgres and MySQL have some very different syntax. Why would you expect this to run without error? — Tim Biegeleisen

Laurenz Albe Laurenz Albe · Accepted Answer · 2017-05-17T07:14:03

The DATATYPE 'string' syntax only works for string literals, and the MICROSECONDS must be part of the string.

You could use a type cast instead:

... current_timestamp - CAST((:itime || ' microseconds') AS interval)

SQL error 42601 when running query in postgreSQL

1 Answers