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

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 29, 2025· 187 views

The PostgreSQL COPY command rejects NULL representation strings that contain newline or carriage return characters to prevent data parsing errors. Use the default \N for text format or empty string for CSV format instead.

Quick fix

Fixes COPY NULL representation cannot use newline or carriage return

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

-- COPY FROM (import)
COPY my_table FROM STDIN WITH (NULL '\\N');
  1. Use the default NULL representation for text format
  2. Use empty string for CSV format NULL representation
  3. Check for literal newlines in your code
  4. Verify your input file for hidden characters
postgresql — error
-- COPY TO (export)
COPY my_table TO STDOUT WITH (NULL '\\N');
-- COPY FROM (import)
COPY my_table FROM STDIN WITH (NULL '\\N');
COPY NULL representation cannot use newline or carriage return

What this error means

PostgreSQL strictly validates the NULL parameter in COPY operations to ensure data integrity. The NULL parameter specifies the string that represents null values during data import/export. PostgreSQL forbids using literal newline (\n) or carriage return (\r) characters in this string because they are used as delimiters in COPY operations. Allowing these characters in the NULL representation could cause the parser to misinterpret data and corrupt your import. This restriction applies to both COPY TO (export) and COPY FROM (import) operations.

Typical symptoms

  • COPY command fails with "ERROR: COPY NULL representation cannot use newline or carriage return"
  • Importing CSV or text files with COPY FROM produces this error
  • Exporting data with COPY TO and a custom NULL parameter fails
  • Data migration tools that use COPY with custom NULL settings fail
  • Error occurs even though your NULL string looks valid in your editor

Common causes

  1. Using a literal newline character in the NULL parameter (e.g., NULL E'\n')
  2. Using a literal carriage return in the NULL parameter (e.g., NULL E'\r')
  3. Accidentally including escape sequences in the NULL string that resolve to newline/carriage return
  4. Copying NULL parameters from text files or scripts that contain hidden whitespace characters
  5. Misconfiguring data export tools that embed newlines in the NULL representation

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

1Use the default NULL representation for text format

For text format COPY operations, use the PostgreSQL default \N (backslash-N) to represent NULL values:

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

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

Or omit the NULL parameter entirely and PostgreSQL will use \N by default:

sql
COPY my_table FROM STDIN;

The backslash-N syntax is safe because it uses a printable character and is explicitly designed for this purpose.

2Use empty string for CSV format NULL representation

CSV format uses an empty string as the default NULL representation:

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

In CSV format, NULL values are written as unquoted empty strings, while actual empty string data is written with double quotes:

bash
-- NULL value represented as empty (unquoted)
id,name,email
1,Alice,alice@example.com
2,,bob@example.com
3,Charlie,

-- With FORCE_NOT_NULL, empty strings become NULL
id,name,email
1,Alice,alice@example.com
2,,bob@example.com
3,Charlie,""
3Check for literal newlines in your code

If you're building the COPY command programmatically, verify your NULL string doesn't contain literal newlines:

Wrong (contains actual newline):

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

Correct (escape the backslash):

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

Using psycopg2:

python
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()
4Verify your input file for hidden characters

If importing from an external file, check for hidden whitespace or unexpected characters:

bash
# 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

Many Windows or Mac editors insert carriage returns that aren't visible. PostgreSQL's COPY parser will catch these and reject your NULL specification.

Python approach to clean data:

python
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")
5Use FORCE_NULL or FORCE_NOT_NULL for edge cases

If you need special handling for NULL values in CSV format, use FORCE_NULL and FORCE_NOT_NULL options:

sql
-- 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)
);

These options override the NULL parameter for specific columns, allowing fine-grained control without using problematic NULL strings.

6Validate NULL representation in scripts and ORMs

If using an ORM or data migration tool, verify it's not injecting problematic characters:

Django (using dumpdata/loaddata):

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

Using Python subprocess to execute COPY safely:

python
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())

Advanced notes

PostgreSQL enforces this restriction at the parser level to prevent data corruption from subtle character issues. Newlines and carriage returns are used by the COPY format (text vs CSV) to delimit rows, and allowing them in the NULL parameter would create ambiguity during parsing. When migrating data between systems, always normalize line endings and NULL representations to PostgreSQL defaults before importing. For high-volume imports, COPY is still the fastest method—using standard NULL representations ensures reliability without sacrificing performance. The binary format for COPY does not support the NULL parameter at all, so binary imports avoid this issue entirely, though at the cost of less human-readable data files. For distributed systems or multi-database setups, standardize on \\N (text) or empty string (CSV) to avoid configuration drift.

Frequently asked questions

What does "COPY NULL representation cannot use newline or carriage return" mean?
PostgreSQL strictly validates the NULL parameter in COPY operations to ensure data integrity. The NULL parameter specifies the string that represents null values during data import/export. PostgreSQL forbids using literal newline (\n) or carriage return (\r) characters in this string because they are used as delimiters in COPY operations.
How do I fix "COPY NULL representation cannot use newline or carriage return"?
The PostgreSQL COPY command rejects NULL representation strings that contain newline or carriage return characters to prevent data parsing errors. Use the default \N for text format or empty string for CSV format instead.
What causes "COPY NULL representation cannot use newline or carriage return"?
Using a literal newline character in the NULL parameter (e.g., NULL E'\n') Using a literal carriage return in the NULL parameter (e.g., NULL E'\r') Accidentally including escape sequences in the NULL string that resolve to newline/carriage return
Is the "COPY NULL representation cannot use newline or carriage return" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading