CODEWITHSUNDEEP
pythonpostgresqlcsv
CodeTrooper
CodeTrooper

Reputation: 1888

PG800 copy functionality throws programming error

I have a function that performs a copy to a PSQL database from a CSV file.

Here's the function: 

def get(self):
        filename = export_user_usermetadata_to_gcs()
        command = """
        INSERT INTO import_temp_table_user_passport_misc
        (profileid, terms_accepted, lastname, firstname, picture_serving_url,
        is_active, is_passport_active, language, created, modified,
        passport_completion_level, email, about_me, uni_code, meta_data)
        VALUES(%s), (%s), (%s), (%s), (%s), (%s), (%s), (%s), (%s), (%s), (%s), (%s), (%s), (%s), (%s)
        """
        location = '/prod-data-migration-csv-exports/{}'.format(filename)
        with gcs.open(location) as data_stream_source:
            reader = csv.reader(data_stream_source)
            slice = itertools.islice(reader, 5000)
            while slice:
                db.executemany(command, slice)
                slice = itertools.islice(reader, 5000)

The table import_temp_table_user_passport_misc has an extra column called autoid which is an incremental integer value.

The error thrown is: ProgrammingError: (u'ERROR', u'ERROR', u'42601', u'INSERT has more target columns than expressions', u'79', u'analyze.c', u'884', u'transformInsertRow'). Do I need to specify a column or a value for the autoincrement column?

Here's the table definition:

CREATE TABLE public.import_temp_table_user_passport_misc
(
    autoid integer NOT NULL DEFAULT nextval('import_temp_table_user_passport_misc_autoid_seq'::regclass),
    profileid text COLLATE pg_catalog."default",
    terms_accepted text COLLATE pg_catalog."default",
    lastname text COLLATE pg_catalog."default",
    firstname text COLLATE pg_catalog."default",
    picture_serving_url text COLLATE pg_catalog."default",
    is_active text COLLATE pg_catalog."default",
    is_passport_active text COLLATE pg_catalog."default",
    language text COLLATE pg_catalog."default",
    created text COLLATE pg_catalog."default",
    modified text COLLATE pg_catalog."default",
    passport_completion_level text COLLATE pg_catalog."default",
    email text COLLATE pg_catalog."default",
    about_me text COLLATE pg_catalog."default",
    uni_code text COLLATE pg_catalog."default",
    meta_data text COLLATE pg_catalog."default",
    CONSTRAINT import_temp_table_user_passport_misc_pkey PRIMARY KEY (autoid)
)

Upvotes: 0

Views: 275

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52949

Your statement has a values table with a bunch of single column rows, but from the looks of it you meant a single row such as VALUES (%s, %s, ..., %s):

command = """
      INSERT INTO import_temp_table_user_passport_misc
      (profileid, terms_accepted, lastname, firstname, picture_serving_url,
      is_active, is_passport_active, language, created, modified,
      passport_completion_level, email, about_me, uni_code, meta_data)
      VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
      """

Upvotes: 1

Related Questions