Vertica Tip: Importing and Exporting Data with Special Delimiters

When moving data in and out of Vertica, it’s common to run into trouble with special characters in your data — especially if they collide with your delimiter, enclosure, or escape character. Characters like commas, quotes, and backslashes can cause fields to break unexpectedly or require transformations that you’d prefer to avoid.

In most examples, you’ll see fields enclosed in quotes and special characters escaped with a backslash (\). That works for many cases, but if your data contains a lot of quotes or backslashes, you’ll end up with duplicated escape sequences (\\) or doubled quotes, making files harder to read and unnecessarily large.

Why Use Non-Printable Delimiters

One of the easiest ways to sidestep these problems is to choose a delimiter that will never appear in your data. A great choice is a control character such as Ctrl-A (E'\001').

Since Ctrl-A doesn’t appear in normal text, Vertica can split fields cleanly without quoting or transforming your actual string values.
This means all of your symbols - from ~!@#$%^&*() to `:;'"?/><`` - can remain untouched.

Avoiding Backslash Duplication

By default, Vertica uses the backslash as the escape character. That means every \ in your data will be doubled on export.
If you want to keep your data byte-for-byte identical, you can replace the escape character with another rarely used control character — for example, Ctrl-B (E'\002').

With escapeAs = E'\002':

• Only the delimiter itself (or the escape char if it appears in data) will be escaped.

• Regular backslashes, quotes, and other special characters are left exactly as they are.

• Files remain smaller and more readable.

Workflow Summary

1. Pick a rare delimiter (like Ctrl-A) so your data never conflicts with it.

2. Change the escape character to something that won’t appear in your data (like Ctrl-B) to avoid unnecessary escaping.

3. Avoid enclosing fields in quotes unless required — with a safe delimiter, quoting isn’t needed.

4. Use matching settings for both EXPORT TO DELIMITED and COPY so that re-import reproduces your original data exactly.

Example Demo

Below is a demo script showing how to:

• Create a test table.

• Load data with special characters using Ctrl-A as the delimiter.

• Export the data with Ctrl-B as the escape character.

• Re-import the file without any transformations.

• Verify the data matches exactly.

cat test.sql
\echo '### 1. Create test table with an id column'
DROP TABLE IF EXISTS my_table CASCADE;
CREATE TABLE my_table(id INT, att4 VARCHAR(200),f3 VARCHAR(10));

\echo '### 2. Load data from STDIN, including special characters and a NULL value'
-- To insert the CTRL-A (\001) character in the vi editor, you need to use a special key sequence.
-- In vi insert mode, press CTRL-V followed by CTRL-A.
-- The CTRL-V key tells vi to insert the next character you type literally, rather than interpreting it as a command.
-- It will appear as ^A in the editor, but it is the single, correct delimiter character.
COPY my_table FROM STDIN
DELIMITER E'\001'
NULL 'REALNULL'
NO ESCAPE
ABORT ON ERROR;
1^A~!@#$%^&()_+=-~!@#$%^&()QWERTYUIOP{}|ASDFGHJKL:"ZXCVBNM<>?][';\\//.,^AAA
2^AREALNULL^ABB
3^A3rd line^ACC
\.

\echo '### 3. Verify initial data load (should show id and the string)'
SELECT * FROM my_table ORDER BY id;

\echo '### 4. Export data to the current directory'
EXPORT TO DELIMITED (
directory = '/home/dbadmin/ALL/DELIMITER/FILES',
filename = 'my_table_export',
escapeAs = E'\002',
delimiter = E'\001',
addHeader = 'true',
nullAs = 'REALNULL',
ifDirExists = 'overwrite'
) AS SELECT * FROM my_table ORDER BY id;

\echo '### 5. View the exported file content'
\! cat /home/dbadmin/ALL/DELIMITER/FILES/my_table_export.csv

\echo '### 6. Truncate the table to prepare for re-import'
TRUNCATE TABLE my_table;
SELECT * FROM my_table; -- Should be empty

\echo '### 7. Import data from a file in the current node into 3 columns:'
COPY my_table
FROM '/home/dbadmin/ALL/DELIMITER/FILES/my_table_export.csv'
SKIP 1
DELIMITER E'\001'
NULL 'REALNULL'
NO ESCAPE
ABORT ON ERROR;

\echo '### 8. Verify data after re-import'
SELECT * FROM my_table ORDER BY id;