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

How do you generate the insert statement? If this is a VARCHAR in PostgreSQL, then the value should be enclosed in quotes in the generated statement. — Mark Rotteveel
– Mark Rotteveel, Commented Nov 21, 2022 at 12:10
Could you add the code that generates this query to your post? — Pompedup
– Pompedup, Commented Nov 21, 2022 at 12:17
I am generating insert query dynamically. I have added the code for this in the post. — Abdulaziz Ibrahim
– Abdulaziz Ibrahim, Commented Nov 21, 2022 at 12:28
You should not create statements through concatenation. Use prepared statements with parameters. Your current code is extremely vulnerable to SQL injection. — Mark Rotteveel
– Mark Rotteveel, Commented Nov 22, 2022 at 9:24

user20042973 · Accepted Answer · 2022-11-22 13:23:42Z

3

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.

edited Nov 22, 2022 at 13:23

answered Nov 21, 2022 at 13:52

user20042973

5,2202 gold badges6 silver badges17 bronze badges

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

2 Comments

Mark Rotteveel Over a year ago

It should look like VALUES ('6375364d1ad809ab6108a544', (single quotes, not double quotes).

user20042973 Over a year ago

Oops, fixed! Also pointed to your warning about the approach overall in the comments

Collectives™ on Stack Overflow

How to insert ObjectId to PostgreSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related