How to insert ObjectId to PostgreSQL

Question

I am writing a script in which I fetch data from MongoDB and dump it into PostgreSQL. Everything works fine but when I try to insert the MongoDBs ObjectId _id, it gives the following error.

(psycopg2.errors.SyntaxError) trailing junk after numeric literal at or near "63711d"

I want to insert the _id in the PostgreSQL db as primary key, so that duplicate rows do not exist.

The query generated by psycopg2 is as follows

[SQL: INSERT INTO employees (_id, candidate_last_name, candidate_first_name, candidate_state, candidate_experience, candidate_relocation, candidate_skills, candidate_specialty)VALUES (6375364d1ad809ab6108a544, NULL, NULL, NULL, NULL, NULL, NULL, NULL), (6375364d1ad809ab6108a545, NULL, NULL, NULL, NULL, NULL, NULL, NULL)]

The _id field in PostgreSQL is VARCHAR.

The code that I am using is as follows:

def insert_psql(db, table_name: str, fields: dict, data: list):

    new_fields = {}
    for field in fields:
        new_fields[field.replace('.', '_')] = fields[field]
    insert_query = f'INSERT INTO {table_name} ('
    insert_query += ', '.join(new_fields.keys()) + ')'
    insert_query += 'VALUES '
    for i, row in enumerate(data):
        insert_query += '('
        for j, field in enumerate(fields):
            if not row.get(field):
                insert_query += 'NULL'
            else:
                insert_query += f'{str(row.get(field))}'
            if not j == len(fields) - 1:
                insert_query += ', '
        insert_query += ')'
        if not i == len(data) - 1:
            insert_query += ', '
    # print(insert_query)
    try:
        db.execute(insert_query)
        db.commit()
    except Exception as e:
        print(e)

The fields dict is a dictionary containing column names and their data_types as value. The data list a list of records to insert

Answer 1

Your error code is: trailing junk after numeric literal at or near "63711d"

Specifically, it is warning about unexpected characters after numeric literals. In the text that is printed we can see five digits (63711) followed by the character a. It seems that the code is attempting to parse this set of characters as a number and failing to do so once it finds the first alpha character.

Indeed when we look at the SQL statement that is generated we can see this:

VALUES (6375364d1ad809ab6108a544,

If you are attempting to insert a string (VARCHAR), then @Mark Rotteveel said what you need to do in the very first comment on this question:

If this is a VARCHAR in PostgreSQL, then the value should be enclosed in quotes in the generated statement.

Your INSERT statement should presumably have something like this instead:

VALUES ('6375364d1ad809ab6108a544',

Edited to use single quotes instead of double. Also, Mark raises another important warning about this approach overall here.