How to escape value special character string export delimited

srijanch0810

given a string with double quotes how to escape character such that it is easier to parse and does not break down into multiple string

"~!@#$%^&amp;()_+=-~!@#$%^&amp;()QWERTYUIOP{}|ASDFGHJKL:"ZXCVBNM<>?][';/.,"

this should be treated as one string, while performing export to delimited query. Also no transformation is allowed on string value.
EXPORT TO DELIMITED (directory='s3://s3bucket/data' , addHeader='true' , escapeAs='' , enclosedBy='\"', delimiter=',') AS SELECT att4 FROM table;

Need guide for how to escape characters with ~, !, @,#,$,%,^,&,*,(,),{,},[,],:,;", '?/><` which parameter we should use such that delimiter should be comma always, how to leverage escape as condition

mosheg

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)