Postgres insert only new rows to the table-CodePudding

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".

CodePudding user response：

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)
    )

CodePudding user response：

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