2

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.

Improve this question
2
  • 1
    there's a lot of mismatch in headers: what you say is your CSV doesn't have grade header, the order of fields is all over the place in further screenshots. "Process Watcher - Import" screenshot specifically tries to insert grade, then score and fails on that. Commented Aug 29, 2022 at 13:54
  • There was indeed grade column as seen here. But with it, import was giving me this error. So I removed grade column from CSV. I felt that the CSV headers will be respected by import tool. But it doesnt seem to be the case. How should I then import this CSV to face no issues? Commented Aug 29, 2022 at 14:21

3 Answers 3

Reset to default
1

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
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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.

Improve this answer

2 Comments

so the csv headers are completely ignored while importing? Because, in CSV, the column headers and values seem in sync.
Don't understand what you mean, but in you copy command, the fourth column you mention, is also the "score" column. Please take a step back, make some coffee and read the error messages. When your content says "A", and your table expects a double precision, you know that you have a problem.
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)
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.