Error while inserting data into a table with array type columns

prad33p2301

Hi
I am using vertica version "Vertica Analytic Database v12.0.4-11" have a table like this

CREATE TABLE public.test
(
id int,
LINKS array[varchar(2048), 250]
);

If I try to insert multiple rows in to the above table like this

insert into test values
(1, '[http://www.somelink123.com/cummins-qsb3-9-engine.html,http://www.facebook.com/]' ),
(1, '[http://www.somelink123.com/somenew-link/somenew-link-reallynew-link/somenewlink-link-new.html,http://www.facebook.com/]')

getting an error which says

09:29:11 START Executing for: 'Vertica' [Vertica], Schema: public
09:29:11 FAILED [INSERT - 0 rows, 0.683 secs] [Code: 9988, SQL State: 22V02] [Vertica]VJDBC **ERROR: Invalid input syntax for type Varchar(80): "http://www.somelink123.com/somenew-link/somenew-link-reallynew-link/somenewlink-link-new.html"
**

strangely although I have defined the type in the array as varchar(2048) I still see varchar(80) for the second row which is having values beyond 80 charecters error while inserting data

at the same time if I try to insert just one value lets say just the second value which is having strings beyond 80 charecters it works!!

What am I missing here?

prad33p2301

Just to add,

I have tried both unbounded and bounded array with different no. of octets mentioned for varchar but somehow I see the error is always Invalid input syntax for type Varchar(80).

Also looked at columns table and that table has the exact charecter limits set during table creation, but Looks like the table definition is disregarded during multiple inserts

SruthiA

please try it the below way. why are you using 250 while defining the array? since you are inserting only one value, varchar(2048) is encough

eonv23=> CREATE TABLE public.test_A
eonv23-> (
eonv23(> id int,
eonv23(> LINKS array[varchar(2048)]
eonv23(> );
CREATE TABLE
eonv23=> insert into test_A values
eonv23-> (1, '[http://www.somelink123.com/cummins-qsb3-9-engine.html,http://www.facebook.com/]' );

OUTPUT

  1

(1 row)

eonv23=>
eonv23=> commit;
COMMIT
eonv23=>

Bryan_H

Hi, we have confirmed this is a product issue. We suggest the following workarounds until a fix is released:
(1) Write the values as array literals w/ string literals inside, rather than as a string literal:
insert into public.avtest2 values
(1, array['http://www.somelink123.com/cummins-qsb3-9-engine.html','http://www.facebook.com/']),
(2, array['http://www.somelink123.com/somenew-link/somenew-link-reallynew-link/somenewlink-link-new.html,http://www.facebook.com/']);

(2) Keep the string literal, but wrap it in string_to_array and explicitly cast it to the target column's bound:
insert into public.avtest2 values
(1, string_to_array('[http://www.somelink123.com/cummins-qsb3-9-engine.html,http://www.facebook.com/]')::array[varchar(2048)]),
(2, string_to_array('[http://www.somelink123.com/somenew-link/somenew-link-reallynew-link/somenewlink-link-new.html,http://www.facebook.com/]')::array[varchar(2048)]);