I have the following input: ,1,2,3. I'd like to cast it to a int[] in postgres.
The following query works:
select string_to_array('1,2,3', ',')::int[]
-- => "string_to_array" => "{1,2,3}"
But this one doesn't:
select string_to_array(',1,2,3', ',')::int[]
-- => ERROR: invalid input syntax for integer: ""
This is because it tries to cast '' (the first value) to an integer.
How can I ignore the first , (or any extra ,) without an error ?
Solution with postgres 9.3+ using array_remove (see Remove array values in pgSQL)
select array_remove(string_to_array(',1,2,3', ','), '')::int[];
-- array_remove
--------------
-- {1,2,3}
This will handle any amount of interspersed , in the array (eg: 1,2,,3, ,1,,,2,3,...)
(Assuming the problem is leading/trailing , from the string)
Use trim:
select string_to_array(trim(both ',' from ',1,2,3,'), ',')::int[];
┌─────────────────┐
│ string_to_array │
├─────────────────┤
│ {1,2,3} │
└─────────────────┘
(1 row)
In case you want to also handle multiple ',' in a row, you can use regexp_replace (with the g (global) flag):
select string_to_array(
trim(both ',' from regexp_replace(',1,,2,,,3', ',,+', ',', 'g')),
',')::int[];
┌─────────────────┐
│ string_to_array │
├─────────────────┤
│ {1,2,3} │
└─────────────────┘
(1 row)
To also have the empty string return NULL, use NULLIF(<string>, ''):
select string_to_array(trim(both ',' from regexp_replace(NULLIF('', ''), ',,+', ',', 'g')), ',')::int[];
┌─────────────────┐
│ string_to_array │
├─────────────────┤
│ (null) │
└─────────────────┘
(1 row)
treat the empty string as null when casting to int[]