Invalid input syntax error for type numeric: V1

Question

I am loading a csv file to create a new table with a column containing a decimal value of 1.449043781.

Here's my code

CREATE TABLE table (
   v1 float
);

Postgres spits out an error saying invalid input syntax error for type numeric even though the value is a float. I have tried changing the data type declaration to decimal(15,13) to no avail. What am I missing here?

Thank you for your input.

Are you using the copy command to load the table? Does your file have a header row? Can you provide the exact message from PostgreSQL? Are there other fields in the file before the number in each row? This value goes into a float with insert so there is no problem there (except that you need the data-type to be "double precision" to preserve ore than 6 digits of precision). — Ron Ballard
The error message means it's trying to interpret the string 'v1' as a number. The fact that the column name v1 appears in the CSV itself suggests that your file has a header row, and the fact that your COPY IN is trying to interpret this row as data means you neglected to specify the HEADER option. — Nick Barnes

Vao Tsun Vao Tsun · Accepted Answer · 2017-12-20T22:26:40

Can't reproduce - copies without errors on 9.6:

t=# CREATE TABLE t (
   v1 float
);
CREATE TABLE
t=# copy t from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1.449043781
>> \.
COPY 1
t=# select v1,pg_typeof(v1) from t;
     v1      |    pg_typeof
-------------+------------------
 1.449043781 | double precision
(1 row)

also from your error, it looks you created table with numeric, not float. And they are not the same (both would accept the 1.449043781 though)

Invalid input syntax error for type numeric: V1

1 Answers