Import .csv file to PostgreSQL and add an autoincrementing ID in the first column

Question

i have downloaded a csv file for testing purposes and would like to upload all of the data to postgresql Database. However, i need to have an autoincrementing ID as the first column of the DB. Initially, i created the DB with SQL Query:

CREATE TABLE pps3
(  id integer NOT NULL DEFAULT 
   nextval('products_product_id_seq'::regclass),
  "brandname" character varying(25),
  "type1" integer,
  "type2" integer,
  "type3" integer,
  "Total" integer )

CSV data:

"brandname","type1","type2","type3","Total"
"brand1","0","0","32","32"
"brand1","0","12","0","12"

I tried to move the data from the CSV with this code:


import csv
import psycopg2

conn = psycopg2.connect("host=localhost dbname=my_django_db user=postgres")
cur = conn.cursor()
with open('PPS-Sep.csv', 'r') as f:
    reader = csv.reader(f)
    next(reader) # Skip the header row.
    for row in reader:
        cur.execute(
        "INSERT INTO pps3 VALUES (%s, %s, %s, %s,%s)",row)

conn.commit()

This is working fine if I do not create the initial ID column.

However, if I run it like that I get an error message that I am trying to insert the brandname to the ID.

Any ideas on how to go around this?

You can reference the columns into which you want to insert like: cur.execute( "INSERT INTO pps3 (brandname, type1, type2, type3, total) VALUES (%s, %s, %s, %s,%s)",row) — JNevill
– JNevill, Commented Oct 17, 2019 at 13:27
i was hoping for a lazier solution because the columns are more than 50.. but i will try that as well :)) — Etika49
– Etika49, Commented Oct 17, 2019 at 13:29
That's SQL though. It wants you to be as explicit as possible and shortcuts generally cause a ton of heartburn. — JNevill
– JNevill, Commented Oct 17, 2019 at 13:33
thanks JNevill ! I will test it now. I think it should work. Do you think that I should put the column names in brackets like this : "brandname"...etc or just state directly — Etika49
– Etika49, Commented Oct 17, 2019 at 13:35
That's up to you. If your column names might be a reserved keyword and you are automating the writing of this code, then toss them in double quotes, otherwise you can leave them off without any hassle. — JNevill
– JNevill, Commented Oct 17, 2019 at 13:40

Łukasz Duda · Accepted Answer · 2019-10-17 13:28:44Z

2

Try change:

INSERT INTO pps3 VALUES (%s, %s, %s, %s)

to

INSERT INTO pps3(type1, type2, type3, Total) VALUES (%s, %s, %s, %s)

When using INSERT INTO without providing columns postgres used all columns from table in original order.

answered Oct 17, 2019 at 13:28

Łukasz Duda

1791 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Import .csv file to PostgreSQL and add an autoincrementing ID in the first column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related