Postgres table import gives "Invalid input syntax for double precision"-CodePudding

I exported a postgres table as CSV:

"id","notify_me","score","active","is_moderator","is_owner","is_creator","show_marks","course_id","user_id"
8,False,36,"A",False,True,True,True,2,8
29,False,0,"A",False,False,False,True,2,36
30,False,25,"A",False,False,False,True,2,37
33,False,2,"A",False,False,False,False,2,40

Then I tried to import it using pgadmin:

But I ended up getting following error:

I checked the values of Score column, but it doesnt contain value "A":

This is the existing data in the coursehistory table (for schema details):

Whats going wrong here?

PS:

Earlier there was grade column with all NULL values:

But it was giving me following error:

I got same error even using \copy

db=# \copy courseware_coursehistory FROM '/root/db_scripts/data/couse_cpp.csv' WITH (FORMAT csv)
ERROR:  value too long for type character varying(2)
CONTEXT:  COPY courseware_coursehistory, line 1, column grade: "NULL"

I felt that import utility will respect the order of column in the header of the csv, especially when there is header switch in the UI. Seems that it doesnt and just decides whether to start from first row or second.

CodePudding user response：

This is your content, with an "A" as the fourth value:

8,False,36,"A",False,True,True,True,2,8

And the your table course_history, with the column "score" in fourth position, using a double precision.

The error message makes sense to me, an A is not a valid double precision.

CodePudding user response：

Order of columns in the kind of import you are doing is relevant. If you need a more flexible way to do imports of csv files, you could use a python script that in fact takes into account your header; and column order is not relevant as long as names, types and no nulls are correct (for existing tables).

Like this:

import pandas as pd
from sqlalchemy import create_engine

engine=create_engine('postgresql://user:password@ip_host:5432/database_name')

data_df= pd.read_csv('course_cpp_courseid22.csv', sep=',', header=0)
data_df.to_sql('courseware_coursehistory', engine, schema='public', if_exists='append', index=False)

CodePudding user response：

I ended up copying this CSV (also shown in postscript of original question; this also contains grade column and has no header row):

using \copy command in psql prompt.

Start psql prompt:

root@50ec9abb3214:~# psql -U user_role db_name

Copy from csv as explained here:

db_name=# \copy db_table FROM '/root/db_scripts/data/course_cpp2.csv' delimiter ',' NULL AS 'NULL' csv