CODEWITHSUNDEEP
postgresql
David Tinker
David Tinker

Reputation: 9634

How to re-map array column values in select in Postgresql?

Is it possible to re-map integer values from a Postgres array column in the select? This is what I have:

select unnest(tag_ids) from mention m where id = 288201;
 unnest  
---------
 -143503
 -143564
  125192
  143604
  137694

tag_ids is integer[] column

I would like to translate those numbers. Functions like abs(unnest(..)) work but found I cannot use a CASE statement. Tx.

Upvotes: 0

Views: 467

Answers (1)

user330315
user330315

Reputation:

If you want to do anything non-trivial with the elements from an array after unnesting, use the set-returning function like table:

select u.tag_id
from mention m   
  cross join unnest(m.tag_ids) as u(tag_id)
where m.id = 288201;

Now, u.tag_id is an integer column that you can use like any other column, e.g. in a CASE expression.

Upvotes: 4

Related Questions