I recently had to do some work with PostgreSQL arrays in a PHP app. My first question: how do I manage arrays as parameterized values in a query?
Turns out there are two ways forward:
Array Literals
These appear in the Postgres documentation as curly brace surrounded, comma delimited lists: {1,2,3}.
A literal like this can be put into a parameter:
/** @var PDO $conn */
$stm = $conn->prepare('INSERT INTO array_table (array_col) VALUES (:v)');
$stm->bindValue(':v', '{1,2,3}');
$stm->execute();
This works okay, but if any of the values of an array have a curly braces or quotes (or any other character that needs escaping) things can get weird. By weird I meant the PHP code will have to handle escaping itself. Which sounds like a recipe for distaster.
Array Constructors
In addition to array literals, Postgres offers an Array constructor syntax: ARRAY[1, 2, 3].
Each one of those values in the ARRAY constructor can be parameterized:
/** @var PDO $conn */
$stm = $conn->prepare('INSERT INTO array_table (array_col) VALUES (ARRAY[:a, :b, :c]::integer[])');
$stm->bindValue(':a', 1, PDO::PARAM_INT);
$stm->bindValue(':b', 2, PDO::PARAM_INT);
$stm->bindValue(':c', 3, PDO::PARAM_INT);
$stm->execute();
Using the array constructor syntax means escaping issues are no longer a concern. It also means that we can bind correct parameter types. This solution is more verbose, however. Non-text data types will require a ::type[] cast as seen in the example above.
Christopher Davis 