Convert array of strings into array of integers

Question

I have the following SQL:

SELECT * FROM (SELECT t.id, t.summary, null as worker, tt.worked from ticket t
INNER JOIN (SELECT ticket, sum(seconds_worked)/3600.0 as worked FROM ticket_time GROUP BY ticket) tt ON tt.ticket=t.id
UNION ALL 
SELECT ticket,null, tt.worker, sum(tt.seconds_worked)/3600.0 from ticket_time tt GROUP BY ticket,worker) as foo
WHERE id in ('9755, 9759') ORDER BY id

The ids string '9755, 9759' in the last line can and will change whenever the sql executed. I can convert the sting to an array like this:

string_to_array('9755, 9759', ',')

But is there a way to convert this array of strings into array of integers?

user330315 · Accepted Answer · 2014-02-11 13:21:30Z

1

Just cast the resulting array to an int[]

where id = ANY ( string_to_array('9755, 9759', ',')::int[] )

answered Feb 11, 2014 at 13:21

user330315

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

3 Comments

konart Over a year ago

Thank you! I'm knew to psql and tried to cast it as ::int4 instead.

Craig Ringer Over a year ago

Or just use an ARRAY literal in the first place, e.g. ANY (ARRAY[9755, 9759])

konart Over a year ago

That was the problem - the core of the system I'm working here can't pass anything but a string, so I have to create an array from it.

Collectives™ on Stack Overflow

Convert array of strings into array of integers

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related