Execute INSERT multiple values if table is empty POSTGRES

Question

I am trying to add some default values in a postgres table. I can add one value but I want to add 5 default values. I've tried this below but it creates only one value since after inserting the first value the empty condition is no longer valid.

records_to_insert = [( 1,'foo', 30, 2, True),
                   ( 2,'bar', 60, 3, False),
                   ( 3,'hero', 95, 2, False),
                   ( 4,'borth', 45, 1, True),
                   ( 5,'here', 30, 2, True)]
sql_insert_query = """ INSERT INTO tester (id, name, time, rating,others)
                SELECT %s,%s,%s,%s,%s
                WHERE NOT EXISTS (SELECT * FROM tester) """
cursor = connection.cursor()
#executemany() to insert 3 rows
result  = cursor.executemany(sql_insert_query, records_to_insert)

I am using python3 and postgres10.5. I've also looked at this, this and this but no dice.

klin · Accepted Answer · 2023-01-07 00:23:09Z

2

Use psycopg2.extras.execute_values(cur, sql, argslist, template=None, page_size=100)

Execute a statement using VALUES with a sequence of parameters. Parameters:

cur – the cursor to use to execute the query.

sql – the query to execute. It must contain a single %s placeholder, which will be replaced by a VALUES list. (...)

You should reformulate the query to use VALUES:

from psycopg2.extras import execute_values

sql_insert_query = """ 
    INSERT INTO tester (id, name, time, rating, others)
    SELECT * FROM (VALUES %s) s
    WHERE NOT EXISTS (SELECT 1 FROM tester) """
execute_values(cursor, sql_insert_query, records_to_insert)

edited Jan 7, 2023 at 0:23

answered Oct 20, 2018 at 23:07

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

Execute INSERT multiple values if table is empty POSTGRES

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related