I keep getting this error trying to insert a record into postgres using exposed v0.53.0
<code>WARN Exposed - Transaction attempt #2 failed: org.postgresql.util.PSQLException: ERROR: null value in column "benefit_id" of relation "benefit" violates not-null constraint
</code>
<code>WARN Exposed - Transaction attempt #2 failed: org.postgresql.util.PSQLException: ERROR: null value in column "benefit_id" of relation "benefit" violates not-null constraint
</code>
WARN Exposed - Transaction attempt #2 failed: org.postgresql.util.PSQLException: ERROR: null value in column "benefit_id" of relation "benefit" violates not-null constraint
This is my table
<code>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")
}
</code>
<code>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")
}
</code>
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")
}
This is my repository method
<code>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)
}
}
}
</code>
<code>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)
}
}
}
</code>
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)
}
}
}
How do I get exposed to generate an id on insert?
I get the same error using the upsert method.
<code>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)
}
}
org.jetbrains.exposed.exceptions.ExposedSQLException: org.postgresql.util.PSQLException: ERROR: null value in column "benefit_id" of relation "benefit" violates not-null constraint
</code>
<code>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)
}
}
org.jetbrains.exposed.exceptions.ExposedSQLException: org.postgresql.util.PSQLException: ERROR: null value in column "benefit_id" of relation "benefit" violates not-null constraint
</code>
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)
}
}
org.jetbrains.exposed.exceptions.ExposedSQLException: org.postgresql.util.PSQLException: ERROR: null value in column "benefit_id" of relation "benefit" violates not-null constraint
We’re already using this database with a spring boot application that is using JOOQ. JOOQ can handle basic crud operations without issue so it doesn’t seem to be an issue with our postgres setup.
you need to add .entityId() after .autoIncrement()
<code>val benefit_id = integer("benefit_id").autoIncrement().entityId()
</code>
<code>val benefit_id = integer("benefit_id").autoIncrement().entityId()
</code>
val benefit_id = integer("benefit_id").autoIncrement().entityId()