erro in cast array

reli

hi, if I exec this query :
SELECT STRING_TO_ARRAY('[95.717,95.718,95.717,95.717,95.717]',',')::ARRAY [float]
I get this error :
SQL Error [3376] [VX001]: [Vertica]VJDBC ERROR: Failed to find conversion function from varchar[] to float[]

mosheg

I've tried the following syntax on Vertica 10.x and it works.

SELECT STRING_TO_ARRAY('[95.717,95.718,95.717,95.717,95.717]', ',');
                STRING_TO_ARRAY
------------------------------------------------
 ["95.717","95.718","95.717","95.717","95.717"]
(1 row)

SELECT STRING_TO_ARRAY('[95.717,95.718,95.717,95.717,95.717]', ',')::ARRAY[FLOAT];
           STRING_TO_ARRAY
--------------------------------------
 [95.717,95.718,95.717,95.717,95.717]
(1 row)

Does it answer your need?

SergeB

@mosheg casting (your second example) works only from 10.0.1 onwards.

reli

I have v9.3.1-7 maybe this is the reason. Thank you!

Pieter_Sheth-Vo

This is a nice discovery, Vertica writes arrays as JSON strings, and STRING_TO_ARRAY can parse JSON strings into arrays:

SELECT STRING_TO_ARRAY('1,2,3,5,8,13,21,34'); / => ["1","2","3","5","8","13","21","34"]
SELECT STRING_TO_ARRAY('1,2,3,5,8,13,21,34') = '["1","2","3","5","8","13","21","34"]'; // => true
SELECT STRING_TO_ARRAY('["1","2","3","5","8","13","21","34"]')= '["1","2","3","5","8","13","21","34"]';// => true
SELECT STRING_TO_ARRAY('["1","2","3","5","8","13","21","34"]')= ARRAY[1,2,3,5,8,13,21,34]; // => true