erro in cast array

relireli Vertica Customer

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[]


Best Answers

  • Options
    moshegmosheg Vertica Employee Administrator
    edited December 2020 Answer ✓

    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]', ',');
    (1 row)
    SELECT STRING_TO_ARRAY('[95.717,95.718,95.717,95.717,95.717]', ',')::ARRAY[FLOAT];
    (1 row)

    Does it answer your need?

  • Options
    SergeBSergeB - Select Field - Employee
    Answer ✓

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


  • Options
    relireli Vertica Customer

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

  • Options

    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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file