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}}
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_subscriptsis 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: