Working with PostgreSQL Arrays in PHP

Working with PostgreSQL Arrays in PHP

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}');

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);

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.