0

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.

Improve this question
4
  • Translate to what? Commented Jul 13, 2021 at 9:59
  • Just different numbers 125192 -> 1234 etc. Commented Jul 13, 2021 at 10:01
  • The please explain the logic base on which the value 125192 is converted to 1234 Commented Jul 13, 2021 at 10:05
  • There are a bunch of these remappings with no underlying logic or relationship between them Commented Jul 13, 2021 at 10:18

1 Answer 1

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.