Kotlin Exposed v0.53.0插入PostgreSQL自增ID非空约束报错求助

报错信息 #

WARN Exposed - Transaction attempt #2 failed: org.postgresql.util.PSQLException: ERROR: null value in column "benefit_id" of relation "benefit" violates not-null constraint

使用upsert方法时同样触发该错误：

org.jetbrains.exposed.exceptions.ExposedSQLException: org.postgresql.util.PSQLException: ERROR: null value in column "benefit_id" of relation "benefit" violates not-null constraint

表定义 #

import org.jetbrains.exposed.sql.Table
import org.jetbrains.exposed.sql.kotlin.datetime.date

object BenefitTable: Table() {
    val benefit_id = integer("benefit_id").autoIncrement()
    override val primaryKey = PrimaryKey(benefit_id)
    val title = varchar("title", 200)
    val description = varchar("description", 200)
    val status = varchar("status", 200)
    val start_date = date("start_date")
    val end_date = date("end_date")
}

仓库方法（插入/更新分支写法） #

fun upsertBenefit(benefit: Benefit): Benefit {
    return transaction {
        if (benefit.benefitId == null) {
            val insertedId = BenefitTable.insert {
                it[title] = benefit.title
                it[description] = benefit.description
                it[status] = benefit.status
                it[start_date] = benefit.startDate
                it[end_date] = benefit.endDate
            } get BenefitTable.benefit_id
            val insertedRow = BenefitTable.selectAll().where { BenefitTable.benefit_id eq insertedId }.single()
            mapBenefitRow(insertedRow)
        } else {
            val benefitId = benefit.benefitId!!
            BenefitTable.update({ BenefitTable.benefit_id eq benefitId }) {
                it[title] = benefit.title
                it[description] = benefit.description
                it[status] = benefit.status
                it[start_date] = benefit.startDate
                it[end_date] = benefit.endDate
            }
            val updatedRow = BenefitTable.selectAll().where { BenefitTable.benefit_id eq benefitId }.single()
            mapBenefitRow(updatedRow)
        }
    }
}

仓库方法（直接使用upsert写法） #

fun upsertBenefit(benefit: Benefit): Benefit {
    return transaction {
        val insertedId = BenefitTable.upsert {
            it[title] = benefit.title
            it[description] = benefit.description
            it[status] = benefit.status
            it[start_date] = benefit.startDate
            it[end_date] = benefit.endDate
        } get BenefitTable.benefit_id
        val insertedRow = BenefitTable.selectAll().where { BenefitTable.benefit_id eq insertedId }.single()
        mapBenefitRow(insertedRow)
    }
}

已知数据库在JOOQ的Spring Boot应用中可正常执行CRUD，排除PostgreSQL配置问题。

解决方案 #

1. 调整表定义，使用PostgreSQL专属自增类型 #

Exposed的autoIncrement()是通用自增定义，在PostgreSQL中推荐直接使用serial()（对应数据库的SERIAL类型，自动绑定序列），替换原有的integer("benefit_id").autoIncrement()：

object BenefitTable: Table() {
    val benefit_id = serial("benefit_id") // 替换原自增定义
    override val primaryKey = PrimaryKey(benefit_id)
    val title = varchar("title", 200)
    val description = varchar("description", 200)
    val status = varchar("status", 200)
    val start_date = date("start_date")
    val end_date = date("end_date")
}

2. 修正upsert方法的冲突字段指定 #

使用upsert时必须明确指定冲突判断的字段（通常为主键），否则Exposed会错误地将主键字段纳入插入字段列表并赋值为null。调整后的upsert写法：

fun upsertBenefit(benefit: Benefit): Benefit {
    return transaction {
        val result = BenefitTable.upsert(conflictIndex = BenefitTable.benefit_id) {
            // 仅当benefitId不为null时设置主键，用于更新逻辑
            benefit.benefitId?.let { id ->
                it[benefit_id] = id
            }
            it[title] = benefit.title
            it[description] = benefit.description
            it[status] = benefit.status
            it[start_date] = benefit.startDate
            it[end_date] = benefit.endDate
        }
        val insertedId = result.get(BenefitTable.benefit_id) ?: 
            throw IllegalStateException("Failed to retrieve inserted/updated benefit ID")
        val row = BenefitTable.select { BenefitTable.benefit_id eq insertedId }.single()
        mapBenefitRow(row)
    }
}

3. 验证数据库表结构 #

执行PostgreSQL命令\d benefit确认benefit_id字段的定义如下（确保存在自增序列和默认值）：

benefit_id | integer | not null default nextval('benefit_benefit_id_seq'::regclass)

4. 开启SQL日志排查生成语句 #

添加Exposed SQL日志，检查生成的插入语句是否错误包含benefit_id=null：

// 在事务初始化前添加
SqlLogger.addLogger(StdOutSqlLogger)

若日志显示插入语句包含benefit_id=null，说明表定义未被Exposed正确识别为自增字段，需回到步骤1调整表定义。

内容的提问来源于stack exchange，提问作者user10993231