CODEWITHSUNDEEP
pythonsqlpostgresql
Kriss
Kriss

Reputation: 161

Postgres insert only new rows to the table

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

Upvotes: 3

Views: 864

Answers (2)

Kriss
Kriss

Reputation: 161

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

Upvotes: 0

mnzbono
mnzbono

Reputation: 131

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

Upvotes: 1

Related Questions