Postgres insert only new rows to the table

user_16431193 0
Viewed 153

I have such a code,

  cursor_local.execute("""SELECT "Name", "Level_Capacity", "Source_NE", "Sink_NE" FROM "table1" WHERE "Name" LIKE '%WDM%' """)
  rows = cursor_local.fetchall()

  psycopg2.extras.execute_batch(cursor_local, 'INSERT INTO table2("Name", "Level_Capacity", "Source_NE", "Sink_NE") VALUES (%s, %s, %s, %s)', (*rows,) )
  connection_local.commit()

and I would like to insert only new records into the table2 from table1 (postgres db), only those that are not in table2. In table2 "Id" is automatically generated and is unique (others are not unique) , when inserting I would like to check if it exists "Name", "Source_NE", "Sink_NE".

postgresql sql python
2 Answers

is "name" unique? maybe you can do an ON CONFLICT Name DO NOTHING

INSERT INTO table2
VALUES ,,,
ON CONFLICT 'Name' DO NOTHING;

else you could do a NOT IN (SELECT "Name" etc FROM table2) etc

INSERT INTO table2
VALUES (
    SELECT * FROM table1 WHERE ('Name' etc)
    NOT IN (SELECT 'Name', etc FROM table2)
    )
user_174810490

it works:

INSERT INTO "table2" ("Name", "Level_Capacity", "Source_NE", "Sink_NE")
SELECT "Name", "Level_Capacity", "Source_NE", "Sink_NE"
FROM "table1" WHERE ("Name", "Source_NE", "Source_Port", "Sink_NE", "Sink_Port") NOT IN (SELECT "Name", "Source_NE", "Source_Port", "Sink_NE", "Sink_Port" FROM "table2");

thank you

user_164311930
Related