POSTGRESQL UNNEST（）与元素编号

Postgres 9.4或更高版本

使用WITH ORDINALITY设置返回function：

当FROM子句中的函数后缀WITH ORDINALITY ， bigint列被附加到从1开始的输出，并为函数输出的每一行递增1。 这在设置返回函数（如UNNEST()的情况下非常有用。

结合pg 9.3+中的LATERAL特性 ，根据pgsql-hacker上的这个线程 ，上面的查询现在可以写成：

 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; 

LEFT JOIN ... ON TRUE保留左表中的所有行，即使右表expression式没有返回任何行。 如果没有关系，你可以使用这个等价的， 不太冗长的forms，隐式的CROSS JOIN LATERAL ：

 SELECT t.id, a.elem, a.nr FROM tbl t, unnest(string_to_array(t.elements, ',')) WITH ORDINALITY a(elem, nr); 

或者更简单，如果基于一个实际的数组 （ arr是一个数组列）：

 SELECT t.id, a.elem, a.nr FROM tbl t, unnest(t.arr) WITH ORDINALITY a(elem, nr); 

甚至用最less的语法：

 SELECT id, a, ordinality FROM tbl, unnest(arr) WITH ORDINALITY a; 

a是自动表和列的别名。 添加的标准列的默认名称是标准的。 但是，添加明确的列别名和表格限定列会更好（更安全，更干净）。

Postgres 8.4 – 9.3

使用row_number() OVER (PARTITION BY id ORDER BY elem)您将根据sorting顺序获取数字，而不是string中原始序号位置的序号 。

你可以简单地省略ORDER BY ：

 SELECT *, row_number() OVER (PARTITION by id) AS nr FROM (SELECT id, regexp_split_to_table(elements, ',') AS elem FROM tbl) t; 

虽然这通常起作用，而且我从来没有在简单的查询中看到过这个问题，但PostgreSQL并没有声明没有ORDER BY的行的顺序。 由于实现细节，它恰好工作。

为了保证以空格分隔的string中的元素的序号 ：

 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; 

或者更简单，如果基于一个实际的数组 ：

 SELECT id, arr[nr] AS elem, nr FROM (SELECT *, generate_subscripts( arr , 1) AS nr FROM tbl) t; 

相关的答案dba.SE：

• 如何保持原始数组中的元素的顺序？

Postgres 8.1 – 8.4

这些function都不可用，但是： RETURNS TABLE ， generate_subscripts() ， unnest() ， array_length() 。
但是这个工作：

 CREATE FUNCTION f_unnest_ord(anyarray, OUT val anyelement, OUT ordinality integer) RETURNS SETOF record LANGUAGE sql IMMUTABLE AS 'SELECT $1[i], i - array_lower($1,1) + 1 FROM generate_series(array_lower($1,1), array_upper($1,1)) i'; 

请注意，数组索引可以不同于元素的序数位置。 考虑这个带有扩展function的演示 ：

 CREATE FUNCTION f_unnest_ord_idx(anyarray, OUT val anyelement, OUT ordinality int, OUT idx int) RETURNS SETOF record LANGUAGE sql IMMUTABLE AS 'SELECT $1[i], i - array_lower($1,1) + 1, i FROM generate_series(array_lower($1,1), array_upper($1,1)) i'; SELECT id, arr, (rec).* FROM ( SELECT *, f_unnest_ord_idx(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; id | arr | val | ordinality | idx ----+-----------------+-----+------------+----- 1 | {a,b,c} | a | 1 | 1 1 | {a,b,c} | b | 2 | 2 1 | {a,b,c} | c | 3 | 3 2 | [5:7]={a,b,c} | a | 1 | 5 2 | [5:7]={a,b,c} | b | 2 | 6 2 | [5:7]={a,b,c} | c | 3 | 7 3 | [-9:-7]={a,b,c} | a | 1 | -9 3 | [-9:-7]={a,b,c} | b | 2 | -8 3 | [-9:-7]={a,b,c} | c | 3 | -7 

比较：

• 标准化1维数组的数组下标，以1开头

尝试：

 select v.*, row_number() over (partition by id order by elem) rn from (select id, unnest(string_to_array(elements, ',')) AS elem from myTable) v 

使用下标生成函数 。
http://www.postgresql.org/docs/current/static/functions-srf.html#FUNCTIONS-SRF-SUBSCRIPTS

例如：

 SELECT id , elements[i] AS elem , i AS nr FROM ( SELECT id , elements , generate_subscripts(elements, 1) AS i FROM ( SELECT id , string_to_array(elements, ',') AS elements FROM myTable ) AS foo ) bar ; 

更简单地说：

 SELECT id , unnest(elements) AS elem , generate_subscripts(elements, 1) AS nr FROM ( SELECT id , string_to_array(elements, ',') AS elements FROM myTable ) AS foo ; 

如果元素的顺序不重要，你可以

 select id, elem, row_number() over (partition by id) as nr from ( select id, unnest(string_to_array(elements, ',')) AS elem from myTable ) a 

unnest2()作为练习

pg v8.4之前的旧版本需要用户定义的unnest() 。 我们可以调整这个旧的函数来返回具有索引的元素：

 CREATE FUNCTION unnest2(anyarray) RETURNS TABLE(v anyelement, i integer) AS $BODY$ SELECT $1[i], i FROM generate_series(array_lower($1,1), array_upper($1,1)) i; $BODY$ LANGUAGE sql IMMUTABLE;