How to escape value special character string export delimited

Answers

• mosheg Vertica Employee Administrator

  August 2025

  Use a delimiter that never appears in your data (e.g., Ctrl-A E'\001') and don’t rely on quotes.
  Please consider the following demo approach, as with a control-char delimiter (Ctrl-A) there’s no need to transform the string (no quoting, no escaping of `~!@#$%^&amp;*(){}[]:;'"?/><`` etc.).

  In addition we use Ctrl-B E'\002' to overwrite the default '\' as the escape char to avoid duplicating '\' in the data.
  To avoid \ sequences in the data when exporting/importing.

  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;
  

  Run time output:

  $ vsql -f test.sql
  ### 1. Create test table with an id column
  DROP TABLE
  CREATE TABLE
  ### 2. Load data from STDIN, including special characters and a NULL value
  ### 3. Verify initial data load (should show id and the string)
   id |                                 att4                                 | f3
  ----+----------------------------------------------------------------------+----
    1 | ~!@#$%^&()_+=-~!@#$%^&()QWERTYUIOP{}|ASDFGHJKL:"ZXCVBNM<>?][';\\//., | AA
    2 |                                                                      | BB
    3 | 3rd line                                                             | CC
  (3 rows)
  
  ### 4. Export data to the current directory
   Rows Exported
  ---------------
               3
  (1 row)
  
  ### 5. View the exported file content
  idatt4f3
  1~!@#$%^&()_+=-~!@#$%^&()QWERTYUIOP{}|ASDFGHJKL:"ZXCVBNM<>?][';\\//.,AA
  2REALNULLBB
  33rd lineCC
  ### 6. Truncate the table to prepare for re-import
  TRUNCATE TABLE
   id | att4 | f3
  ----+------+----
  (0 rows)
  
  ### 7. Import data from a file in the current node into 3 columns:
   Rows Loaded
  -------------
             3
  (1 row)
  
  ### 8. Verify data after re-import
   id |                                 att4                                 | f3
  ----+----------------------------------------------------------------------+----
    1 | ~!@#$%^&()_+=-~!@#$%^&()QWERTYUIOP{}|ASDFGHJKL:"ZXCVBNM<>?][';\\//., | AA
    2 |                                                                      | BB
    3 | 3rd line                                                             | CC
  (3 rows)
  

  0