Xata•5w 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();

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

12 Replies

ghqpioOP•5w ago

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

cmck•5w ago

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•5w ago

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

cmck•4w ago

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).

const insert = await db.insertInto('person')
  .values({first_name: 'John', person_id: 1})
  .onConflict((oc) =>
    oc.column('person_id').doUpdateSet((eb) => ({
      first_name: eb.ref('excluded.first_name'),
    })),
  )
  .execute();

const insert = await db.insertInto('person')
  .values({first_name: 'John', person_id: 1})
  .onConflict((oc) =>
    oc.column('person_id').doUpdateSet((eb) => ({
      first_name: eb.ref('excluded.first_name'),
    })),
  )
  .execute();

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

ghqpioOP•4w ago

One moment

ghqpioOP•4w ago

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•4w ago

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

cmck•4w ago

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•4w ago

{ "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•4w ago

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•4w ago

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

cmck•4w ago

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

Gaming

Programming

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