Cannot Upsert on a View

[MIRO1990]

What version of GraphJin are you using? graphjin version 0.17.15

Steps to reproduce the issue (config used to run GraphJin).

Create a view from multiple tables, add "instead of" triggers to the view so it can be updated and inserted into but all the business logic is under our direct control.

Expected behaviour and actual result.

Expected: upsert into the view exactly as you would with a table Actual: an invalid SQL query is generated

the problem is the view cannot have unique/primary key constraints so the generated SQL query has an empty "on conflict" clause. Is there a way to specify which columns in the view need to be unique? I tried doing this through the tables section in the config yaml but couldn't get it to work.

[dosco]

Try this in the config under tables set the primary key column as below:

tables:
  - name: my_view
     columns:
      - name: some_id
        primary: true

table: paper_ranking

[MIRO1990]

@dosco Thank you very much :-) That's worked for us. Really appreciate your help!!! :-)

[MIRO1990]

@dosco sorry we didn't fully test it before closing the issue. Marking it as primary key in yml config does generate a valid sql query. however, when that query fires we get this error ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification which is caused by the id field not actually having a unique index constraint.

[dosco]

id field should be s primary key