COPY command issue

Question

I'm using this to read data into the table I created in Postgres 9.2:

COPY tagdata FROM 'C:/Filter112595/QF112595_3.csv' WITH DELIMITER ',' 
    CSV HEADER FORCE_NOT_NULL;

Data types are real, integer and date.

I get this error:

ERROR:  invalid input syntax for type real: "NULL"
CONTEXT:  COPY tagdata, line 2, column residual: "NULL"

Before using FORCE_NOT_NULL, I had NULL as '' but changed it because of the different data types.

Can someone explain what's going on?

What do the first two lines of the "QF112592_3.csv" file look like? Showing the table DDL would probably help too. — bma, Aug 31 '13 at 23:14

score 2 · Answer 1 · answered Sep 01 '13 at 21:44

You've told PostgreSQL that no columns are null, so NULL in the column must mean a number. Since NULL isn't a valid floating point number, you get an error.

You haven't really given the information required to help you properly, but my guess is that you need to specify FORCE_NOT_NULL only for a subset of the columns. See the syntax for copy:

    FORCE_NOT_NULL ( column_name [, ...] ) |

In questions like this you really should provide a couple of lines of the CSV so we can see what's really going on, and when you make statements like:

Before using FORCE_NOT_NULL, I had NULL as '' but changed it because of the different data types.

you need to explain that - what "different data types", why'd you need to change it, what problem you thought you were solving, links to any previous related SO questions, etc.

COPY command issue

1 Answers1