Xata•13mo ago

Need help with: invalid SQL: table [main/excluded] not found

Using Kysely as a dialect. Trying to insert data and resolve conflict by using native PG "excluded" table when running into conflict.
My code:

return await this._db
      .insertInto('Reports')
      .values(values)
      .onConflict((oc) =>
        oc.column('id').doUpdateSet((eb) => ({
          name: eb.ref('excluded.name'),
        })),
      )
      .execute();

return await this._db
      .insertInto('Reports')
      .values(values)
      .onConflict((oc) =>
        oc.column('id').doUpdateSet((eb) => ({
          name: eb.ref('excluded.name'),
        })),
      )
      .execute();

Hitting an error:

functions: Error: invalid SQL: table [main/excluded] not found

functions: Error: invalid SQL: table [main/excluded] not found

ghqpioOP•11/22/24, 7:27 AM

Any idea how should I approach conflict resolution in such case?

cmck•11/22/24, 4:20 PM

Hey ghqpio, thanks for reaching out. I'll look into recreating this issue and see if I can find a workaround. Let me know if you have any success in the meantime.

ghqpioOP•11/22/24, 5:02 PM

No success so far. Thank you for looking into it!

cmck•11/25/24, 4:58 PM

I've not been able to recreate this error yet. The below code allows me to update a person table with columns person_id, first_name, last_name, age (and also the xata columns).

Can you try setting up an insert with onConflict excluded in a separate file to see if it works?

ghqpioOP•11/25/24, 6:23 PM

One moment

ghqpioOP•11/25/24, 6:40 PM

Getting the same error even when copy-pasting your code and executing twice.
First time to create record. Second time to resolve conflict.
Any suggesting what else can be debugged to resolve the issue?

ghqpioOP•11/26/24, 12:08 AM

Did the same with the native xata sql editor. Any chance it is related to my db configuration somewhere behind the scenes?

cmck•11/26/24, 9:43 AM

Ok I think you're right, it may be a permissions issue specific to your database/branch. I'll investigate in the backend to see what I can find. Can you please share your workspace ID and any request ID of a failed on conflict insert?

Thanks for your patience.

ghqpioOP•11/26/24, 6:14 PM

{
"id": "7b308321-317c-9b47-ab2e-a3d5fe2e6f53",
"message": "invalid SQL: table [pickbyai:main/exluded] not found"
}

Workspace name: PickByAi_17
Workspace slug: PickByAi_17-mfo2t1

cmck•11/27/24, 11:47 AM

Hi ghqpio, I've been able to recreate this error. The issue happens on databases that are not enabled for direct postgres connection. This setting can only be enabled on database creation so you may need to drop and recreate in order to use ON CONFLICT ... EXCLUDED.

ghqpioOP•11/27/24, 5:57 PM

Drop the entire db, you mean? Any solution that doesn't require dropping the database? It's a production database.

cmck•11/28/24, 9:37 AM

Unfortunately, there's no other workaround. You would have to export and import the database into a Postgres-enabled DB in order to use ON CONFLICT... EXCLUDED.
https://xata.io/docs/csv-data/import-data