The text 'null' is being sent to a double precision column
ERROR: invalid input syntax for type double precision: "null"Short answer
PostgreSQL only turns the keyword NULL into a null value. The quoted word 'null' is plain text, and text cannot be read as a double precision number, so the cast fails. Convert the text with NULLIF(value, 'null') before casting, or tell COPY which text means null with the NULL option.
Fix it fast
1Find out which cause you have
Find out which rows hold the word instead of a real null. Run this on the text column that feeds the failing cast (names are examples).
SELECT value_text, count(*) FROM staging WHERE value_text !~ '^[-+0-9.eE]+$' GROUP BY value_text;2Match what you see, then jump to its fix
What does the error look like?
ERROR: invalid input syntax for type double precision: "null"
Cause 1 of 2
The text null reaches a cast in SQL
1Check
A text column or a bound parameter holds the four letters null. In SQL, only the bare keyword NULL is a null value, so the cast has to parse the letters as a number and cannot.
SELECT count(*) FROM staging WHERE value_text = 'null';2Fix
Let NULLIF turn the placeholder into a real null before the cast. NULLIF returns a null when its two arguments are equal, and a null casts to double precision without any error.
INSERT INTO readings (amount)
SELECT NULLIF(value_text, 'null')::double precision
FROM staging;3Verify
The insert completes and the rows that held the word now contain a null.
SELECT count(*) FROM readings WHERE amount IS NULL;Confirmed by postgresql.org
Cause 2 of 2
A text or CSV file spells null as the word null
1Check
COPY reads \N as null in text format and an unquoted empty string in CSV format. Any other spelling is treated as data, so the word null is handed to the number parser.
grep -n -m 5 'null' data.csv2Fix
Tell COPY which string stands for null. Any data item that matches this string is stored as a null value.
COPY readings (id, amount)
FROM '/path/data.csv'
WITH (FORMAT csv, HEADER, NULL 'null');3Verify
The load finishes and the amount column shows nulls where the file had the word.
SELECT count(*) FROM readings WHERE amount IS NULL;Confirmed by postgresql.org
How do I stop it coming back?
- Send a real null from the application (a parameter bound as null), never the string 'null'.
- Load files into a text staging table first, clean them with NULLIF, then cast into the final table.
- Use the same NULL string when you export with COPY TO and import with COPY FROM.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Related fixes
Databases
COPY fails on a CSV with invalid input syntax for an integer column
ERROR: invalid input syntax for integer
COPY stops at the first value it cannot convert.
4 causes, each with a fixDatabases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fix