0

I have a question that's very similar to other SO questions, but I haven't been able to find my exact case. I would like to update a PostgreSQL table as follows:

update my_table
    set my_timestamp = null
 where TO_CHAR(my_timestamp :: DATE, 'dd-mm-yyyy') = '01-01-1970' and cast(my_timestamp as time) > '10:10:10'

The problem is setting my_timestamp = null violates a uniqueness constraint. In these cases I would like to skip that particular row. So I added the following line to my code.

update my_table
    set my_timestamp = null
 where TO_CHAR(my_timestamp :: DATE, 'dd-mm-yyyy') = '01-01-1970' and cast(my_timestamp as time) > '10:10:10'
ON CONFLICT ON CONSTRAINT my_constraint do nothing

But this gives a syntax error and I can't figure out the exact right way to fix it. What's the right way to do this?

jss367
  • 4,759
  • 14
  • 54
  • 76
  • 1
    [As documented in the manual](https://www.postgresql.org/docs/current/sql-insert.html) `on conflict` is only valid for INSERT –  Nov 10 '22 at 12:24
  • I think this [answer](https://stackoverflow.com/a/8289253/7889624) is close to your problem – I T Nov 10 '22 at 20:00
  • You did not indicate which version of Postgres you have. But if you have v15 you can try the `merge` statement. – Belayer Nov 10 '22 at 20:03

0 Answers0