SQL How to dynamically compare date column with equal operator in Postgres

Question

I want to insert data into a table only when current dateTime1 column is not equal to the existing dateTime1 column in the table. I tried running the query below but it does not return valid result when mydate1 is null in the table.

INSERT INTO TableA (id, dateTime1, datetime2)
SELECT 123, mydate1, mydate2
WHERE NOT EXISTS (SELECT 1 FROM tableA 
WHERE id=123 and dateTime1 <> mydate1)

Basically I want to insert data into the table only when the date column is not equal to the current date column that we are trying to insert.

why not using unique constraints?.. and eg insert ... on conflict?.. — Vao Tsun
– Vao Tsun, Commented Nov 30, 2017 at 7:06

Kaushik Nayak · Accepted Answer · 2017-11-30 05:45:56Z

1

Use COALESCE to avoid NULLs.

So, If you are comparing dates, use constructs like

<>  COALESCE ( mydate1, someotherdate )

where someotherdate is any date which always makes the condition to be false (or true if you need it that way).

edited Nov 30, 2017 at 5:45

answered Nov 30, 2017 at 5:02

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL How to dynamically compare date column with equal operator in Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related