How to easily convert a comma seperated list of ids into a table of rows



  • I want to compare a comma-separated list of IDs to the result of a query, something like this:

    select * from  (values (2201322723),
    (2201318904),
    (2201299380),
    (2164969641),
    (5682476934),
    (2201314720),
    ) as t(object_id)
    EXCEPT
    select object_id from star.dim_partner where entity = 'partners' and is_active is true;
    

    The above works, however, it required me to use a text editor with column based support to add round brackets (the list was actually quite a bit longer).

    Is there and easier/better way to convert a comma separated list into a (temp) table with PostgreSQL?

    a bit like:

    select item as object_id from ARRAY[6058271246, 2201299240, 1801904030, 2097401903];
    

    ?

    This will them allow me to cut-and-paste any list of IDs in between the square brackets.



  • You can use string_to_array() together with unnest()

    select * 
    from unnest(string_to_array('2201322723,2201318904,2201299380,2164969641', ',')) as t(object_id)
    

    Or if you are using Postgres 14 or later this can be simplified to

    select * 
    from string_to_table('2201322723,2201318904,2201299380,2164969641', ',') as t(object_id)
    

Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2