How to fix COPY NULL representation cannot use newline or carriage return in PostgreSQL

-- COPY TO (export)
COPY my_table TO STDOUT WITH (NULL '\\N');

-- COPY FROM (import)
COPY my_table FROM STDIN WITH (NULL '\\N');

COPY my_table FROM STDIN;

COPY my_table TO STDOUT WITH (FORMAT CSV, NULL '');
COPY my_table FROM STDIN WITH (FORMAT CSV, NULL '');

-- NULL value represented as empty (unquoted)
id,name,email
1,Alice,[email protected]
2,,[email protected]
3,Charlie,

-- With FORCE_NOT_NULL, empty strings become NULL
id,name,email
1,Alice,[email protected]
2,,[email protected]
3,Charlie,""

null_str = "\n"  # This is a literal newline
copy_cmd = f"COPY table FROM STDIN WITH (NULL '{null_str}')"

null_str = "\\N"  # This is backslash followed by N
copy_cmd = f"COPY table FROM STDIN WITH (NULL '{null_str}')"

import psycopg2

conn = psycopg2.connect("dbname=mydb user=postgres")
cur = conn.cursor()

# Use format option to avoid newline issues
with open("data.csv", "r") as f:
    cur.copy_from(f, "my_table", null="\\N")

conn.commit()

# View hidden characters in your file
cat -A data.txt | head

# Check for carriage returns (Windows line endings)
file data.txt

# Convert Windows (CRLF) to Unix (LF) line endings
dos2unix data.txt

with open("data.csv", "r") as infile, open("data_clean.csv", "w") as outfile:
    for line in infile:
        # Remove carriage returns and trailing whitespace
        outfile.write(line.rstrip("\r\n") + "\n")

-- Treat unquoted empty strings as NULL
COPY my_table FROM STDIN WITH (
  FORMAT CSV,
  FORCE_NULL (name, email)
);

-- Treat quoted empty strings as non-NULL
COPY my_table FROM STDIN WITH (
  FORMAT CSV,
  FORCE_NOT_NULL (name, email)
);

-- Use both simultaneously
COPY my_table FROM STDIN WITH (
  FORMAT CSV,
  FORCE_NULL (name),
  FORCE_NOT_NULL (email)
);

# Django uses JSON by default, avoiding COPY NULL issues
python manage.py dumpdata myapp > dump.json
python manage.py loaddata dump.json

import subprocess

# Build COPY command with escaped NULL
copy_cmd = "COPY my_table FROM STDIN WITH (NULL '\\\\N')"
ps = subprocess.Popen(
    ["psql", "-h", "localhost", "-U", "postgres", "-d", "mydb"],
    stdin=subprocess.PIPE,
    stdout=subprocess.PIPE,
    stderr=subprocess.PIPE
)

with open("data.txt", "rb") as f:
    stdout, stderr = ps.communicate(input=copy_cmd.encode() + b"\n" + f.read())
    if ps.returncode != 0:
        print("Error:", stderr.decode())