inserting a record into postgres database using 2 where conditions

Question

I'm trying to insert data into a table based on whether a record exists with the same ID and DATE. I've put the code in a loop that does the insertion query for every record in the students_present variable. HERE'S THE CODE

for ID in students_present:
    STUDENTID = 'SELECT STUDENTID FROM encodings WHERE STUDENTID = ID'
    STUDENTNAME = 'SELECT STUDENTNAME FROM encodings WHERE STUDENTID = ID'
    STUDENTEMAIL = 'SELECT STUDENTEMAIL FROM encodings WHERE STUDENTID = ID'
    insert_script = "INSERT INTO meeting1(STUDENTID, STUDENTNAME, STUDENTEMAIL, MEETINGDATE, MEETINGTIME) WHERE ID IN STUDENTID AND MEETINGDATE <> GETDATE() VALUES ('{}','{}','{}','GETDATE()','CURRENT_TIMESTAMP')".format(STUDENTID, STUDENTNAME, STUDENTEMAIL)
    cur.execute(insert_script)

I believe my error arrises from how I've written the condition as it reflects this error:

syntax error at or near "WHERE"
LINE 1: INSERT INTO meeting1 WHERE ID IN STUDENTID AND MEETINGDATE <...

Any advice on how to go about this?

I think you have a [similar problem](https://stackoverflow.com/questions/32039059/sql-insert-query-with-condition/32039178#answer-32039178). TLDR Insert does not support WHERE, that's a job for UPDATE — Muslimbek Abduganiev, Oct 19 '21 at 11:58
Why are you running three SELECTs to get three different columns from the same row? That should be only one SELECT. You can most probably get rid of the slow FOR loop and do this in a single `INSERT INTO () SELECT ...` statement — , Oct 19 '21 at 12:05
Read [the docs for `INSERT` in Postgres](https://www.postgresql.org/docs/9.5/sql-insert.html): a `WHERE` clause is not supported (nor is it supported in any major RDMBS, at least that I'm aware of). — esqew, Oct 19 '21 at 14:58
Please use your preferred search engine to research your inquiry before posting, in accordance with [ask]. This is a duplicate of [Postgres: INSERT if does not exist already](https://stackoverflow.com/questions/4069718/postgres-insert-if-does-not-exist-already) — esqew, Oct 19 '21 at 15:00

score 0 · Answer 1 · answered May 17 '22 at 13:52

I created a Unique constraint key for the particular columns that I wanted to be unique by running

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2);

This will create a key that is a combination of column1 and column2 for which a record cannot exist with the same values in both columns.

for example if column1 is named Month and column2 named Year, I can only have one record like May 2022 for the Month and Year columns respectively. Once I enter another record with both month and year as May 2022 I'll get a duplicate key error on that constraint.

I hope this helps someone.

inserting a record into postgres database using 2 where conditions

1 Answers1