I have a column in a PostgreSQL database which is a bigint.
So far I am not able to find a way formulate a PDO insert in PHP.
PHP Predefined Constants only lists PDO::PARAM_INT
. When I use this in my statement, I get the following error. Note, I get the same error even if I don't include the data type in the bindValue
statement. From what I understand, by omitting the type PHP is using PDO::PARAM_INT
based on the variable type.
Uncaught exception 'PDOException' with message 'SQLSTATE[22003]: Numeric value out of range: 7 ERROR: value "2978878787878787878"; is out of range for type integer' in index.php:163
How do I store a BIGINT in MySQL using PDO? is a question here which talks about the same issue. However, they go right into using string as the database column type. While this is a nice workaround, it doesn't take into account if you are unable to change the database types.
Is there a way of using bigint with PDO's in PHP that doesn't require modifications to the database?
Example Code
$query = "INSERT INTO clients ";
$query .= "(timestamp, name, value) ";
$query .= "VALUES (now(), :name, :value) ";
$query .= "RETURNING id";
$name = "Bob Probert";
$value = 2978878787878787878; // note this is less than bigint max of 9223372036854775807
$stmt->bindValue(':name', $name, PDO::PARAM_STR);
$stmt->bindValue(':value', $value, PDO::PARAM_INT);
Table
Table "db.clients"
Column | Type | Modifiers | Storage | Stats target | Description
---------------+-----------------------------+------------------------------------------------------+----------+--------------+-------------
id | integer | not null default nextval('tickets_id_seq'::regclass) | plain | |
timestamp | timestamp without time zone | | plain | |
name | character varying(40) | | extended | |
value | bigint | not null | plain | |
Indexes:
"tickets_pkey" PRIMARY KEY, btree (id)