The LONG VARCHAR data type

Moderator: NorbertKrupa

Post Reply
User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1775
Joined: Sat Jan 21, 2012 4:58 am
Contact:

The LONG VARCHAR data type

Post by JimKnicely » 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).

Code: Select all

[dbadmin@vertica01 ~]$ vsql -c "create table test_65001 (col1 varchar(65001));"
ERROR 3852:  Length for type varchar cannot exceed 65000
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):

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
Oops, we can’t do that!

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
That worked.

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
4. Finally, let’s load the random data from the file created in step 3 into the TEST_LONG_VARCHAR2 table:

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)
Have fun!
Jim Knicely

Image

Note: I work for HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

Mrao
Newbie
Newbie
Posts: 22
Joined: Mon Feb 03, 2014 2:07 pm

Re: The LONG VARCHAR data type

Post by Mrao » Mon Feb 03, 2014 5:31 pm

Easy and Useful info. Thanks!

padme
Newbie
Newbie
Posts: 1
Joined: Thu Apr 23, 2015 6:18 am

Re: The LONG VARCHAR data type

Post by padme » Thu Apr 23, 2015 6:27 am

what is the data type in c++ sdk that represents for long varchar? The document(http://my.vertica.com/docs/7.1.x/HTML/i ... B%7C_____3) only mention that VString => varchar/char.

Post Reply

Return to “Vertica Tips, Lessons and Examples”