The LONG VARCHAR data type
Posted: Thu Jan 09, 2014 8:27 pm
Hi!
The maximum size for the VARCHAR data type in Vertica is 65,000 bytes. So we cannot create a table having a column with the data type and size of VARCHAR(65001).
However, now with Vertica 7, we have the LONG VARCHAR data type. Its maximum size is 32 MB!
Example
1. Let’s try to create a table containing a LONG VARCHAR(32000001):
Oops, we can’t do that!
2. Next, we’ll create a table storing a LONG VARCHAR(32000000):
That worked.
3. To test, we’ll create a file containing 32,000,000 random ASCII characters:
4. Finally, let’s load the random data from the file created in step 3 into the TEST_LONG_VARCHAR2 table:
Have fun!
The maximum size for the VARCHAR data type in Vertica is 65,000 bytes. So we cannot create a table having a column with the data type and size of VARCHAR(65001).
Code: Select all
[dbadmin@vertica01 ~]$ vsql -c "create table test_65001 (col1 varchar(65001));"
ERROR 3852: Length for type varchar cannot exceed 65000
Example
1. Let’s try to create a table containing a LONG VARCHAR(32000001):
Code: Select all
[dbadmin@vertica01 ~]$ vsql -c "create table test_long_varchar2 (col1 long varchar(32000001));"
ERROR 3852: Length for type long varchar cannot exceed 32000000
2. Next, we’ll create a table storing a LONG VARCHAR(32000000):
Code: Select all
[dbadmin@vertica01 ~]$ vsql -c "create table test_long_varchar2 (col1 long varchar(32000000));"
CREATE TABLE
3. To test, we’ll create a file containing 32,000,000 random ASCII characters:
Code: Select all
[dbadmin@vertica01 ~]$ base64 /dev/urandom | head -c 32421052 > /home/dbadmin/file_32000000.txt
[dbadmin@vertica01 ~]$ cat /home/dbadmin/file_32000000.txt | tr -d "\r \n" > /home/dbadmin/file_32000000_2.txt
[dbadmin@vertica01 ~]$ ls -lrt /home/dbadmin/file_32000000_2.txt
-rw-rw-r--. 1 dbadmin dbadmin 32000000 Jan 9 11:42 /home/dbadmin/file_32000000_2.txt
[dbadmin@vertica01 ~]$ wc -c /home/dbadmin/file_32000000_2.txt
32000000 /home/dbadmin/file_32000000_2.txt
Code: Select all
[dbadmin@vertica01 ~]$ vsql -c "copy test_long_varchar2 from '/home/dbadmin/file_32000000_2.txt';"
Rows Loaded
-------------
1
(1 row)
[dbadmin@vertica01 ~]$ vsql -c "select length(col1) from test_long_varchar2;"
length
----------
32000000
(1 row)