PostgreSQL unnest with element number

Question

When I have a column with separated values  I can use the unnest   function   myTable id   elements --- ------------ 1   ab cd efg hi 2   jk lm no pq 3   rstuv wxyz  select id  unnest string to array elements        AS elem from myTable  id   elem --- ----- 1    ab 1    cd 1    efg 1    hi 2    jk       How can I include element numbers  I e    id   elem   nr --- ------ --- 1    ab     1 1    cd     2 1    efg    3 1    hi     4 2    jk     1       I want the original position of each element in the source string  I ve tried with window functions  row number    rank   etc   but I always get 1  Maybe because they are in the same row of the source table   I know it s a bad table design  It s not mine  I m just trying to fix it

User · Answer

Use Subscript Generating Functions  http   www postgresql org docs current static functions-srf html FUNCTIONS-SRF-SUBSCRIPTS  For example   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     More simply   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

User · Answer

unnest2   as exercise  Older versions before pg v8 4 need a user-defined unnest    We can adapt  this old function to return elements with an index   CREATE FUNCTION unnest2 anyarray    RETURNS setof record  AS  BODY    SELECT  1 i   i   FROM   generate series array lower  1 1                            array upper  1 1   i   BODY  LANGUAGE sql IMMUTABLE

User · Answer

If the order of element is not important  you can   select    id  elem  row number   over  partition by id  as nr from     select       id        unnest string to array elements        AS elem   from myTable   a

User · Answer

Postgres 9 4 or later Use WITH ORDINALITY for set-returning functions   When a function in the FROM clause is suffixed by WITH ORDINALITY  a bigint column is appended to the output which starts from 1 and increments by 1 for each row of the function s output  This is most useful in the case of set returning functions such as unnest     In combination with the LATERAL feature in pg 9 3   and according to this thread on pgsql-hackers  the above query can now be written as  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 preserves all rows in the left table  even if the table expression to the right returns no rows  If that s of no concern you can use this otherwise equivalent  less verbose form with an implicit CROSS JOIN LATERAL  SELECT t id  a elem  a nr FROM   tbl t  unnest string to array t elements        WITH ORDINALITY a elem  nr    Or simpler if based off an actual array  arr being an array column   SELECT t id  a elem  a nr FROM   tbl t  unnest t arr  WITH ORDINALITY a elem  nr    Or even  with minimal syntax  SELECT id  a  ordinality FROM   tbl  unnest arr  WITH ORDINALITY a   a is automatically table and column alias  The default name of the added ordinality column is ordinality  But it s better  safer  cleaner  to add explicit column aliases and table-qualify columns  Postgres 8 4 - 9 3 With row number   OVER  PARTITION BY id ORDER BY elem  you get numbers according to the sort order  not the ordinal number of the original ordinal position in the string  You can simply omit ORDER BY  SELECT    row number   OVER  PARTITION by id  AS nr FROM   SELECT id  regexp split to table elements       AS elem FROM tbl  t   While this normally works and I have never seen it fail in simple queries  PostgreSQL asserts nothing concerning the order of rows without ORDER BY  It happens to work due to an implementation detail  To guarantee ordinal numbers of elements in the blank-separated 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   Or simpler if based off an actual array  SELECT id  arr nr  AS elem  nr FROM   SELECT    generate subscripts arr  1  AS nr FROM tbl  t  Related answer on dba SE   How to preserve the original order of elements in an unnested array   Postgres 8 1 - 8 4 None of these features are available  yet  RETURNS TABLE  generate subscripts    unnest    array length    But this works  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    Note in particular  that the array index can differ from ordinal positions of elements  Consider this demo with an extended 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  Compare   Normalize array subscripts for 1-dimensional array so they start with 1

User · Answer

Try   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

[sql] PostgreSQL unnest() with element number

Examples related to sql

Examples related to arrays

Examples related to postgresql

Examples related to window-functions

Examples related to set-returning-functions