How can I cast an array of string into an array of integers?
Below is my array
["6", "5"]
I want convert into int array
[6, 5]
1
-
Do you have a simple postgres array or a JSON array? That looks like a JSON array...S-Man– S-Man2021-02-26 15:45:52 +00:00Commented Feb 26, 2021 at 15:45
Add a comment
|
2 Answers
SELECT
array_agg(elems::int)
FROM unnest(ARRAY['5', '6']) as elems
- Expand the array into one record per element
- Reaggregate cast integer values
To ensure the original order, you need to add WITH ORDINALITY, which adds an index to the original array:
SELECT
array_agg(elems.value::int ORDER BY elems.index)
FROM unnest(ARRAY['5', '6']) WITH ORDINALITY as elems(value, index)
If you have a JSON array instead, the algorithm is the same, only the used functions have different names:
SELECT
json_agg(elems.value::int ORDER BY elems.index)
FROM json_array_elements_text('["5", "6"]'::json) WITH ORDINALITY as elems(value, index)
EDIT: According to comment:
this is my query. SELECT data->>'pid' FROM user_data where data->>'pid' is not null How can I update pid to array of integers ?
You have to expand and reaggregate nonetheless:
SELECT
json_agg(elems::int) -- 2
FROM user_data,
json_array_elements_text(data -> 'pid') as elems -- 1
WHERE data->>'pid' IS NOT NULL
GROUP BY id
4 Comments
s_m94
this is my query. SELECT data->>'pid' FROM user_data where data->>'pid' is not null How can I update pid to array of integers ?
S-Man
Please add such information into your questions. Add some sample data and the expected output.
s_m94
How can I update all pid to array of integer with update statement ?
S-Man
That's the correct UPDATE. You need to use jsonb_set(): dbfiddle.uk/…
You can’t “cast”, but you can get something very close to a cast:
array(select unnest(myArray)::int)
As a testable query:
select array(select unnest(array['5', '6'])::int)
See live demo.
When applying to a column of a selected table:
select
array(select unnest(myArrayCol)::int)
from myTable
See live demo.
This syntax preserves order.
