PostgreSQL unnest () avec numéro d’élément

Quand j’ai une colonne avec des valeurs séparées, je peux utiliser la fonction unnest() :

 myTable id | elements ---+------------ 1 |ab,cd,efg,hi 2 |jk,lm,no,pq 3 |rstuv,wxyz select id, unnest(ssortingng_to_array(elements, ',')) AS elem from myTable id | elem ---+----- 1 | ab 1 | cd 1 | efg 1 | hi 2 | jk ... 

Comment puis-je inclure des numéros d’éléments? C’est à dire:

 id | elem | nr ---+------+--- 1 | ab | 1 1 | cd | 2 1 | efg | 3 1 | hi | 4 2 | jk | 1 ... 

Je veux la position d’origine de chaque élément dans la chaîne source. J’ai essayé avec les fonctions de fenêtre ( row_number() , rank() etc.) mais je reçois toujours 1 . Peut-être parce qu’ils sont dans la même ligne de la table source?

Je sais que c’est un mauvais design de table. Ce n’est pas le mien, j’essaye juste de le réparer.

    Postgres 9.4 ou plus tard

    Utilisez WITH ORDINALITY pour les fonctions de retour de set:

    Lorsqu’une fonction de la clause FROM est suffixée par WITH ORDINALITY , une colonne bigint est ajoutée à la sortie qui commence à 1 et s’incrémente de 1 pour chaque ligne de la sortie de la fonction. Ceci est particulièrement utile dans le cas de fonctions de retour telles que UNNEST() .

    En combinaison avec la fonctionnalité LATERAL de la pg 9.3+ et selon ce thread sur pgsql-hackers , la requête ci-dessus peut maintenant être écrite comme suit :

     SELECT t.id, a.elem, a.nr FROM tbl AS t LEFT JOIN LATERAL unnest(ssortingng_to_array(t.elements, ',')) WITH ORDINALITY AS a(elem , nr ) ON TRUE; 

    LEFT JOIN ... ON TRUE conserve toutes les lignes de la table de gauche, même si l’expression de la table à droite ne renvoie aucune ligne. Si cela ne vous concerne pas, vous pouvez utiliser ce formulaire autrement équivalent et moins verbeux avec un LATERAL CROSS JOIN LATERAL implicite:

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

    Ou plus simple si basé sur un tableau réel ( arr étant une colonne de tableau):

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

    Ou même avec une syntaxe minimale:

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

    a est automatiquement un alias de tableau et de colonne. Le nom par défaut de la colonne de ordinalité ajoutée est l’ ordinality . Mais il est préférable (plus sûr, plus propre) d’append des alias de colonnes explicites et des colonnes qualifiées.

    Postgres 8.4 – 9.3

    Avec row_number() OVER (PARTITION BY id ORDER BY elem) vous obtenez des nombres en fonction de l’ordre de sorting, pas du nombre ordinal de la position ordinale d’origine dans la chaîne.

    Vous pouvez simplement omettre la ORDER BY :

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

    Bien que cela fonctionne normalement et que je ne l’ai jamais vu se casser dans des requêtes simples, PostgreSQL n’affirme rien concernant l’ordre des lignes sans ORDER BY . Il arrive à travailler en raison d’un détail d’implémentation.

    Pour garantir des nombres ordinaux d’éléments dans la chaîne séparée par des blancs:

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

    Ou plus simple si basé sur un tableau réel :

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

    Réponse sur dba.SE:

    • Comment conserver l’ordre d’origine des éléments dans un tableau non nested?

    Postgres 8.1 – 8.4

    Aucune de ces fonctionnalités n’est encore disponible: RETURNS TABLE , generate_subscripts() , unnest() , array_length() .
    Mais cela fonctionne:

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

    Notez en particulier que l’index du tableau peut différer des positions ordinales des éléments. Considérez cette démo avec une fonction étendue :

     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 

    Comparer:

    • Normaliser les indices de tableau pour un tableau à une dimension afin qu’ils commencent par 1

    Essayer:

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

    Utilisez les fonctions de génération d’indices .
    http://www.postgresql.org/docs/current/static/functions-srf.html#FUNCTIONS-SRF-SUBSCRIPTS

    Par exemple:

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

    Plus simplement:

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

    Si l’ordre des éléments n’est pas important, vous pouvez

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

    unnest2() comme exercice

    Les versions antérieures à pg v8.4 nécessitent un unnest() défini par l’ unnest() . Nous pouvons adapter cette ancienne fonction pour renvoyer des éléments avec un index:

     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;