Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

The text 'null' is being sent to a double precision column

ErrorERROR: 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.

  • Applies to PostgreSQL 16, PostgreSQL 17, PostgreSQL 18
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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;
  2. 2Match what you see, then jump to its fix

    1. 1The failing statement contains a cast such as value_text::double precision, or the driver sends the parameter as a string.The text null reaches a cast in SQLFix
    2. 2The error appears during COPY, and the file has the word null in numeric columns.A text or CSV file spells null as the word nullFix

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

Did this fix it?

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.csv

2Fix

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

Did this fix it?

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

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.