is unnest supported? #958

Activity

1. maratori commented on Mar 30, 2021

   

   maratori

   on Mar 30, 2021

   More actions

   I successfully use following query with unnest

   -- name: InsertOutdatedEntity :exec
   INSERT INTO outdated_entities (entity_type, entity_id, outdated_at)
   SELECT @entity_type, unnest(@ids::UUID[]), @outdated_at
   ON CONFLICT (entity_type, entity_id) DO UPDATE
       SET outdated_at = GREATEST(outdated_entities.outdated_at, EXCLUDED.outdated_at);
2. tcastelly commented on Mar 30, 2021

   

   tcastelly

   on Mar 30, 2021

   Author

   More actions

   Thank you for your answer.

   I can compile your query.
   In fact I can compile this:

   SELECT unnest(ARRAY[48]::int[])

   But because I need multiple arrays in unnest it looks like I have to use:

   SELECT * FROM unnest(ARRAY[48]::int[], ARRAY['{}']::json[]);

   and here it's broken:

   function unnest(unknown, unknown) does not exist

   👍React with 👍1Reacted by Brennan Lamey
3. maratori commented on Mar 30, 2021

   

   maratori

   on Mar 30, 2021

   More actions

   Have you tried multiple unnests for multiple arrays? Something like this should work:

   -- name: InsertOutdatedEntity :exec
   INSERT INTO outdated_entities (entity_type, entity_id, outdated_at)
   SELECT unnest(@entity_types::VARCHAR[]), unnest(@ids::UUID[]), @outdated_at;
4. tcastelly commented on Mar 30, 2021

   

   tcastelly

   on Mar 30, 2021

   Author

   More actions

   Oh yes thank you :)
5. tcastelly

   closed this as completedon Mar 30, 2021
6. tcastelly commented on Jun 17, 2022

   

   tcastelly

   on Jun 17, 2022

   Last edited by tcastelly

   Author

   More actions

   I'm sorry to re-open this old issue. But it seems than the "unnest" example from the Postgres documentation is not working with sqlc.
   I'm wondering if it's something can be fixed or no.

   https://www.postgresql.org/docs/current/functions-array.html

   select * from unnest(ARRAY[1,2], ARRAY['foo','bar','baz']) as x(a,b);

   1,foo
   2,bar
   <null>,baz
   

   result: https://play.sqlc.dev/p/29816032c2fa5f604524ad3089121ef2284bf59c84d871ede995f78bcd4f11c1

   Split unsset can be build by sqlc but it's not a solution because the result of the query is not the same:

   select * from unnest(ARRAY[1,2]) as a, unnest(ARRAY['foo','bar','baz']) as b;

   1,foo
   1,bar
   1,baz
   2,foo
   2,bar
   2,baz
   

   Thank you very much.

   👍React with 👍5Reacted by Gurpartap Singh, Carlana, Brennan Lamey, Somtochi Onyekwere and j4mesim
7. tcastelly

   reopened this on Jun 17, 2022
8. earthboundkid commented on Mar 10, 2023

   

   earthboundkid

   on Mar 10, 2023

   Contributor

   More actions

   I have had this same issue. My workaround was to use JSONB instead of a pair of ARRAYs, but it's ugly.
9. tcastelly commented on Apr 5, 2023

   

   tcastelly

   on Apr 5, 2023

   Last edited by tcastelly

   Author

   More actions

   I have had this same issue. My workaround was to use JSONB instead of a pair of ARRAYs, but it's ugly.

   Hi @carlmjohnson do you have an example? Because I've tried something like this but it's not working

   WITH to_update as (
       SELECT x
       FROM JSON_TO_RECORDSET(@auth_users::JSON)
                AS x("id" BIGINT, "label" VARCHAR(55))
   )
   UPDATE auth_users
   SET label = to_update.label
   FROM to_update
   WHERE auth_users.id = to_update.id
     AND auth_users.user_id = @user_id;

   Issue: #2191

   Thank you :)
10. earthboundkid commented on Apr 5, 2023

    

    earthboundkid

    on Apr 5, 2023

    Contributor

    More actions

    https://github.com/spotlightpa/almanack/blob/d48b9e2167754e56b729788fe04d1270f00c38af/sql/queries/gdocs_image.sql#L6

    👍React with 👍6Reacted by Thomas CASTELLY, Kyle Gray, Fedor Kanin, Jacob Mischka, Kirill Tkachuk and yihaoye
11. kyleconroy

    added

    bugSomething isn't workingSomething isn't working

    📚 postgresql

    analyzer

    on Sep 22, 2023
12. kyleconroy commented on Sep 22, 2023

    

    kyleconroy

    on Sep 22, 2023

    Collaborator

    More actions

    Going to close this out as we have a pretty good workaround.

    👎React with 👎20Reacted by Brennan Lamey, Brendan Lo, Lauren, Triwandha Febian, Patrick, Nikolay Latyshev, Alexandr Kalmyk, Jack Carmichael, yihaoye, Illia Dymura and 10 more
13. kyleconroy

    closed this as completedon Sep 22, 2023
14. yihaoye commented on Jan 16, 2026

    

    yihaoye

    on Jan 16, 2026

    Last edited by yihaoye

    More actions

    beside jsonb_array, there are another 2 way which maybe better in performance if array too large for json marshal in code

    postgresql 10+ could do the following uses parallel arrays evaluated in lockstep, it will return [(1, a), (2, b), (3, null), (4, null)]

    SELECT 
        unnest(ARRAY[1, 2, 3, 4]) as id, 
        unnest(ARRAY['a', 'b']) as name;

    there is 3rd way to work around by WITH ORDINALITY + JOIN

    WITH pairs AS (
      SELECT 
        a.val::bigint AS num,
        b.val::uuid AS id,
        c.val::varchar AS description
      FROM unnest(@array_a::bigint[]) WITH ORDINALITY AS a(val, ord)
      JOIN unnest(@array_b::uuid[]) WITH ORDINALITY AS b(val, ord) ON a.ord = b.ord
      JOIN unnest(@array_c::varchar[]) WITH ORDINALITY AS c(val, ord) ON o.ord = c.ord
    )
    SELECT pairs.num,
           pairs.id,
           pairs.description,
    FROM pairs

    👍React with 👍3Reacted by Carlana, vux-qualgo and kevin-ip