Question 114915 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres table import gives "Invalid input syntax for double precision"

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:

enter image description here

But I ended up getting following error:

enter image description here

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

enter image description here

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

enter image description here

Whats going wrong here?

PS:

Earlier there was grade column with all NULL values:

enter image description here

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.

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

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

    enter image description here

    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

  2. 0

    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.

    Login or Signup to reply.
  3. 0

    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)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search