generate_subscripts(array, 2) returns two records when there is only one multidimensional element

Question

Why does this return 2 records when there is only one multidimensional element in multidimensional array images?

SELECT images
FROM  (
   SELECT images, generate_subscripts(images, 2) AS s
   FROM listings
   WHERE listings.id = 2
   ) as foo;

Note: I shortened the base64 string for easier viewing.

id               | 2
created_at       | 2017-04-19 23:44:50.150913+00
posted_by        | 10209280753550922
images           | {{/9j/4AAJRgAB2dgKd/9k=,3/2/image-3-2-1492645490308.jpeg}}

---

dev_dolphin_db=# SELECT images FROM(SELECT images, generate_subscripts(images, 2) AS s FROM listings where listings.id = 2) as foo;
-[ RECORD 1 ]----------------------------------------------------------------------------------------------------------------------
images | {{/9j/4AAQSkZJRdgKd/9k=,3/2/image-3-2-1492645490308.jpeg}}
-[ RECORD 2 ]----------------------------------------------------------------------------------------------------------------------
images | {{/9j/4AAQSkZN2dgKd/9k=,3/2/image-3-2-1492645490308.jpeg}}

Answer 1

There are two elements in your array, separated by the comma:

{{/9j/4AAJRgAB2dgKd/9k=,3/2/image-3-2-1492645490308.jpeg}}

See:

SELECT *
FROM unnest('{{/9j/4AAJRgAB2dgKd/9k=,3/2/image-3-2-1492645490308.jpeg}}'::text[])

unnest
--------------------------------
/9j/4AAJRgAB2dgKd/9k=
3/2/image-3-2-1492645490308.jpeg

generate_subscripts() returns one row per element in the specified dimension (not one row per dimension). The manual:

generate_subscripts is a convenience function that generates the set of valid subscripts for the specified dimension of the given array. Zero rows are returned for arrays that do not have the requested dimension, or for NULL arrays (but valid subscripts are returned for NULL array elements).

If that's supposed to be a single element, it would have to be double-quoted to escape the special meaning of the comma:

{{"/9j/4AAJRgAB2dgKd/9k=,3/2/image-3-2-1492645490308.jpeg"}}

Aside: in modern Postgres you can use this simpler equivalent query:

SELECT images
FROM   listings, generate_subscripts(images, 2) s
WHERE  id = 2;

That's an implicit CROSS JOIN LATERAL. See:

• What is the difference between LATERAL and a subquery in PostgreSQL?