Db col unique and nullable versus Kysely query

[Wouter Hendriks]

I've got this db col:

<d:integer name="process" ondelete="cascade" references=".processes" unique="true" />

Triggers a warning:

"Columns that are both unique and nullable are not supported, disabled nullability"

Ok, I can remove "unique", no problem.

However, then this Kysely code throws an error:

  await whdb.db<WvuawDB>()
    .insertInto("wvuaw.ai_processes")
    .values([
      {
        process: processId,
        conclusion: sentiment_is_positive ? "price_accepted" : "price_rejected",
      }
    ])
    .onConflict((oc) =>
      oc.column("process")
        .doUpdateSet({ conclusion: whdb.sql`excluded.conclusion` })
    )
    .execute();

Error:

 ** UNEXPECTED EXCEPTION: there is no unique or exclusion constraint matching the ON CONFLICT specification

/Users/wouter/projects/webhare/whtree/jssdk/whdb/vendor/postgresql-client/src/connection/connection.ts:185:25: Called from Connection.query
node:internal/process/task_queues:105:5: Called from process.processTicksAndRejections
/Users/wouter/projects/webhare/whtree/jssdk/whdb/src/impl.ts:322:14: Called from async <anonymous>
/Users/wouter/projects/webhare/whtree/node_modules/kysely/dist/cjs/dialect/postgres/postgres-driver.js:75:28: Called from async PostgresConnection.executeQuery
/Users/wouter/projects/webhare/whtree/node_modules/kysely/dist/cjs/query-executor/query-executor-base.js:37:28: Called from async
/Users/wouter/projects/webhare/whtree/node_modules/kysely/dist/cjs/driver/default-connection-provider.js:12:20: Called from async DefaultConnectionProvider.provideConnection
/Users/wouter/projects/webhare/whtree/node_modules/kysely/dist/cjs/query-executor/query-executor-base.js:36:16: Called from async DefaultQueryExecutor.executeQuery

...

Removing `unique="true"` gets rid of the error but adds the linter warning.

Is there some way to have it work without the warning? (I've only added `unique="true"` because of the error).

[Arnold Hendriks]

On Wednesday, May 7, 2025 at 8:11:00 PM UTC+2 Wouter Hendriks wrote:

I've got this db col:

<d:integer name="process" ondelete="cascade" references=".processes" unique="true" />

Triggers a warning:

"Columns that are both unique and nullable are not supported, disabled nullability"

Error:

 ** UNEXPECTED EXCEPTION: there is no unique or exclusion constraint matching the ON CONFLICT specification

Oh cool - no more "Database error" unrecognizable errors. I was hoping the postgrejs update would fix that.

 

Is there some way to have it work without the warning? (I've only added `unique="true"` because of the error).

Have you tried removing the check that blocks this?

IF (basecol.isunique AND basecol.nullable)

{

INSERT INTO retval.msgs(iserror, code, text, line, col) VALUES(FALSE, 0, "Columns that are both unique and nullable are not supported, disabled nullability", col->linenum, 0) AT END;

basecol.nullable := FALSE;

}

This might not be an issue anymore, especially if you only access the data from TS. This was not supported with the dbserver (as it would allow you to store only a single 0 value as it considered NULL to be 0)

You could also try manually adding the constraint to the database using create unique index - https://www.postgresql.org/docs/current/sql-createindex.html