How to Get Element Numbers When UNNESTing Comma-Separated Values in PostgreSQL?

Use the UNNEST() function in PostgreSQL and get the element number

When working with columns containing comma-separated values ​​in PostgreSQL, you can use the unnest() function to extract individual elements. However, if you need to include element numbers in your output, there are a few approaches you can consider.

PostgreSQL 14 and higher

Use the string_to_table() function:

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

PostgreSQL 9.4 and higher

For functions that return a collection, use WITH ORDINALITY:

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

Or, based on the actual array:

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

PostgreSQL 8.4 - 9.3 versions

Replace row_number() OVER (PARTITION BY id) with ORDER BY:

<code class="language-sql">SELECT id, elem, row_number() OVER (PARTITION by id) AS nr
FROM (SELECT id, regexp_split_to_table(elements, ',') AS elem FROM tbl);</code>

PostgreSQL 8.1 - 8.4 versions

Use a function to simulate this functionality:

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

SELECT id, arr, (rec).*
FROM (
   SELECT *, f_unnest_ord(arr) AS rec
   FROM (
      VALUES
        (1, '{a,b,c}'::text[])  -- short for: '[1:3]={a,b,c}'
      , (2, '[5:7]={a,b,c}')
      , (3, '[-9:-7]={a,b,c}')
      ) t(id, arr)
   ) sub;</code>

The above is the detailed content of How to Get Element Numbers When UNNESTing Comma-Separated Values in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!