I'm a newbie in python. Im trying to insert an array into my database and where an array already exists, should be updated. Below is my code snippet.
query = """
SELECT array_remove(ARRAY[(SELECT id FROM s_id f WHERE f.s_id=u.s_id AND f.type_id=2),
(SELECT id FROM s_id f WHERE f.s_id=u.n_id AND f.type_id=1)],NULL)
FROM table_a u
"""
cur.execute(query)
#fetch all rows
link_ids = cur.fetchall()
for count,link_id in enumerate(link_ids):
print(count+1, "...row")
table_b = str(link_id[0]).replace('[', '{').replace(']','}')
check_existance_qry = """
SELECT * from table_b where table_b_arr = '{}'
""".format(table_b_arr)
cur.execute(check_existance_qry)
results = cur.fetchall()
rows_returned =len(results)
if rows_returned == 0: #insert
query_1 = """
INSERT INTO table_b (table_b_arr) VALUES ('{}')
""".format(table_b_arr)
cur.execute(query_1)
conn.commit()
else: #update
query_2 = """
UPDATE table_b SET updated_at = now(), table_b_arr = '{0}' WHERE table_b_arr = '{0}'
""".format(table_b_arr)
cur.execute(query_2)
conn.commit()
conn.close()
cur.close()
I have an excluded constraint
on table table_b to prevent insertion of an array element that already exists. i would like my code to update where if an array element already exist.
say array {123,321}
already exists in my table, if a new entry say {123,543}
appears then array {123,321}
should be updated to {123,543}
.
Apparently, when i run my code. it inserts then when an array with a matching element is found, it terminates the process. Thank you
