CODEWITHSUNDEEP
sqlpostgresql
era s'q
era s'q

Reputation: 591

How to insert in postgres if column_date has not passed?

I have two tables: table1 and table2. Table1 contails event_Id , event_date. Table2 contains event_Id as a foreign key, booked_on, email(Primary Key). At present, I'm inserting data in table2 if there is no conflict and selecting data from table1 in the same query.

WITH NEW_ROW AS ( INSERT INTO TABLE1(EVENT_ID,BOOKED_ON,EMAIL) VALUES('SOME ID',NOW()) ON CONFLICT DO NOTHING RETURNING EVENT_ID ) SELECT TEMPLATE_ID FROM TABLE2 WHERE EVENT_ID IN (SELECT EVENT_ID FROM NEW_ROW

Now, I want to insert data in table2 only if event_date has not yet passed as mentioned in table1 in the same query. I came up with the code but I'm don't know how to insert it in the current query.

TABLE1.EVENT_DATE::DATE - NOW()::DATE)>=1

EDIT: Table1 sample insertion:

INSERT INTO TABLE1(EVENT_ID,EVENT_DATE)VALUES('EVENT1','2/9/2021'),('EVENT2','4/9/2021');

Table2 insertion:

INSERT INTO TABLE2(EMAIL,EVENT_ID,BOOKED_ON)VALUES('[email protected]',EVENT1,NOW());

INSERT INTO TABLE2(EMAIL,EVENT_ID,BOOKED_ON)VALUES('[email protected]',EVENT2,NOW());

Here, Event1 has already passed. So, no new row should get inserted in table2. Whereas Event2 has not passed and an entry for this event can be made if not already made.

Upvotes: 0

Views: 123

Answers (1)

Rahul Biswas
Rahul Biswas

Reputation: 3467

Please check this. If an event already exists in table2 and you don't want to add it in table2 then use NOT EXISTS.

-- MySQL
INSERT INTO TABLE2(EMAIL,EVENT_ID,BOOKED_ON)         
SELECT '[email protected]' EMAIL
     , t.EVENT_ID
     , NOW()     
FROM TABLE1 t
WHERE t.EVENT_ID = 'EVENT1'
     AND t.EVENT_DATE > NOW()
     AND NOT EXISTS (SELECT 1
                     FROM TABLE2
                     WHERE EVENT_ID = t.EVENT_ID);

Upvotes: 1

Related Questions