How to fix 2200B: escape_character_conflict in PostgreSQL

-- Problematic: Same character for DELIMITER and ESCAPE
COPY table_name FROM '/path/to/file.csv'
WITH (FORMAT csv, DELIMITER '|', ESCAPE '|');

-- Solution: Use different characters
COPY table_name FROM '/path/to/file.csv'
WITH (FORMAT csv, DELIMITER '|', ESCAPE '\');

-- Also check for QUOTE and ESCAPE conflicts
COPY table_name FROM '/path/to/file.csv'
WITH (FORMAT csv, QUOTE '"', ESCAPE '"');  -- Problematic

COPY table_name FROM '/path/to/file.csv'
WITH (FORMAT csv, QUOTE '"', ESCAPE '\');  -- Correct

-- Problematic: Ambiguous backslash usage
SELECT E'Path: C:\Users\Name\File.txt';

-- Solution 1: Double the backslashes
SELECT E'Path: C:\\Users\\Name\\File.txt';

-- Solution 2: Use dollar-quoted strings instead
SELECT $$Path: C:UsersNameFile.txt$$;

-- Solution 3: Use Unicode escapes carefully
SELECT E'Unicode: αβγ';  -- Greek letters αβγ

-- Common escape sequences:
-- \\ = backslash
-- \' = single quote
-- \" = double quote
-- \b = backspace
-- \f = form feed
-- \n = newline
-- \r = carriage return
-- \t = tab
-- \uXXXX = Unicode character (4 hex digits)
-- \UXXXXXXXX = Unicode character (8 hex digits)
-- \xXX = byte with hex value XX
-- \ooo = byte with octal value ooo

-- Check current setting
SHOW standard_conforming_strings;

-- When standard_conforming_strings = on (default):
-- Backslashes are treated literally in regular strings
-- Use E'...' for escape sequences

-- When standard_conforming_strings = off:
-- Backslashes are escape characters in all strings
-- Legacy behavior for compatibility

-- If you need legacy behavior:
SET standard_conforming_strings = off;

-- But better to update your code:
-- Change: 'Line 1
Line 2'
-- To: E'Line 1\nLine 2' or $$Line 1
Line 2$$

-- For literal backslashes in data:
INSERT INTO table (path) VALUES (E'C:\\Windows\\System32');
-- Or with standard_conforming_strings = on:
INSERT INTO table (path) VALUES ('C:\Windows\System32');

-- For COPY commands, preprocess data or change escape character
-- Example: Data contains backslashes, but backslash is escape character

-- Option 1: Change escape character
COPY table_name FROM '/path/to/file.csv'
WITH (FORMAT csv, ESCAPE '~');  -- Use tilde instead of backslash

-- Option 2: Escape the escape characters in your data
-- Before: Column1|Column2WithBackslash|Column3
-- After:  Column1|Column2\WithBackslash|Column3

-- Option 3: Use different format
COPY table_name FROM '/path/to/file.txt'
WITH (FORMAT text, DELIMITER '|');  -- TEXT format doesn't use escape by default

-- For programmatic insertion, use parameterized queries
const query = 'INSERT INTO table (data) VALUES ($1)';
const values = ['Text with \ backslash and ' quote'];
await client.query(query, values);  -- Driver handles escaping

-- Or escape manually in SQL:
INSERT INTO table (data) VALUES (E'Text with \\ backslash and \' quote');

-- 1. Dollar-quoted strings (no escape conflicts)
SELECT $$This string can contain 'quotes', "double quotes", and ackslashes$$;

-- 2. Dollar-quoted with tag
SELECT $tag$This can contain $$ signs too$tag$;

-- 3. Unicode string literals
SELECT U&'d\0061t\+000061';  -- 'data' (hex escapes)
SELECT U&'d\0061t\+000061';    -- Same, with +

-- 4. Two single quotes for literal quote
SELECT 'It''s raining';  -- It's raining

-- 5. For multiline strings, use concatenation
SELECT 'Line 1'
       'Line 2'
       'Line 3';

-- 6. For complex strings, consider storing in a file and using \include
-- Or use your application layer to handle escaping

-- Test individual string literals
SELECT E'\tTab' as test1;  -- Should work
SELECT '\tTab' as test2;   -- Depends on standard_conforming_strings

-- Test COPY with minimal data
CREATE TEMP TABLE test_escape (id int, data text);

-- Create a test file
\echo '1|Column with \ backslash' > /tmp/test.csv
\echo '2|Column with | pipe' >> /tmp/test.csv

-- Test different escape characters
COPY test_escape FROM '/tmp/test.csv'
WITH (FORMAT csv, DELIMITER '|', ESCAPE '\');

-- Check what was loaded
SELECT * FROM test_escape;

-- Test with problematic data
\echo '3|Data with "quote' > /tmp/test2.csv
COPY test_escape FROM '/tmp/test2.csv'
WITH (FORMAT csv, DELIMITER '|', QUOTE '"', ESCAPE '\');

-- Use \copy for client-side testing (handles file access differently)
\copy test_escape FROM '/tmp/test.csv' WITH (FORMAT csv, DELIMITER '|', ESCAPE '~')