How to Get Element Numbers with PostgreSQL's unnest() Function?

Use PostgreSQL’s unnest() function to get the element number

The unnest() function is useful when working with columns containing delimited values. However, getting the element value and its number simultaneously can be a challenge.

PostgreSQL 14 or higher

For comma separated strings, using the string_to_table() function provides a simple solution:

<code class="language-sql">SELECT t.id, a.elem, a.nr
FROM tbl t
LEFT JOIN LATERAL string_to_table(t.elements, ',') WITH ORDINALITY AS a(elem, nr) ON true;</code>

PostgreSQL 9.4 or higher

With ORDINALITY added in PostgreSQL 9.4 to functions that return collections, you can use the following query:

<code class="language-sql">SELECT t.id, a.elem, a.nr
FROM tbl AS t
LEFT JOIN LATERAL unnest(string_to_array(t.elements, ',')) WITH ORDINALITY AS a(elem, nr) ON true;</code>

PostgreSQL 8.4 - 9.3

To ensure the sequence number of elements in a string:

<code class="language-sql">SELECT id, arr[nr] AS elem, nr
FROM (
   SELECT *, generate_subscripts(arr, 1) AS nr
   FROM (SELECT id, string_to_array(elements, ' ') AS arr FROM tbl) t
   ) sub;</code>

PostgreSQL 8.1 - 8.4

For versions of PostgreSQL prior to 8.4, you can create a function to achieve the desired result:

<code class="language-sql">CREATE FUNCTION f_unnest_ord(anyarray, OUT val anyelement, OUT ordinality integer)
  RETURNS SETOF record
  LANGUAGE sql IMMUTABLE AS
'SELECT [i], i - array_lower(,1) + 1
 FROM   generate_series(array_lower(,1), array_upper(,1)) i';</code>

The above is the detailed content of How to Get Element Numbers with PostgreSQL's unnest() Function?. For more information, please follow other related articles on the PHP Chinese website!