CODEWITHSUNDEEP
postgresql
mrc
mrc

Reputation: 3153

on conflict postgresql statement works with joins?

I am using on conflict statement to do an upsert, but I get an error: Syntax error near to line 58: ON CONFLICT (id_producto_final) DO.

The query statement is:

     Insert into table1 (id, value1, description, isactivated)
     select a.id, b.value1, c.description, d.isactivated
     from tableA as a
     left join tableB as b on b.id=a.id1
     left join tableC as c on c.id=a.id2
     left join tableD as d on d.id=a.id3
     ON CONFLICT (id) DO UPDATE SET isactivated= EXCLUDED.isactivated

It neither works with do nothing, so is not excluded table error. So I am guessing that maybe are joins the error source, but I do no even know why.

Can anyone help?

Upvotes: 1

Views: 1589

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51456

https://www.postgresql.org/docs/9.5/static/sql-insert.html

ON CONFLICT can be used to specify an alternative action to raising a unique constraint or exclusion constraint violation error. (See ON CONFLICT Clause below.)

https://www.postgresql.org/docs/9.4/static/sql-insert.html - no such option. ON CONFLICT is interpreted starting from 9.5

https://www.postgresql.org/docs/9.5/static/release-9-5.html

Major enhancements in PostgreSQL 9.5 include:

Allow INSERTs that would generate constraint conflicts to be turned into UPDATEs or ignored

Upvotes: 3

Related Questions