Insert IF NOT EXISTS
I'm attempting to insert a row to a table, but only if a row doesnt already exist which has the same value for the column "content" and if it does, return that row like this:
const [resource] = await db
.insert(resources)
.values({ content: 'John Doe' })
.onConflictDoNothing({ target: resources.content })
.returning();
It doesn't seem to work, i dont get any errors but now i never seem to insert new rows or return existing rows. I'm brand new to Drizzle, what stupid mistake am I making?5 Replies
Your
content column needs to have a UNIQUE constraintThis is a Postgresql behavior not exclusive to Drizzle: ON CONFLICT is an optional clause that triggers a particular action when the INSERT statement raises a UNIQUE constraint violation
DbVisualizer
PostgreSQL Upsert: INSERT ON CONFLICT Guide
In this guide, you will dig into PostgreSQL Upsert and INSERT ON CONFLICT statements, a tool offered by PostgreSQL to perform upserts in SQL. Let's get started!
ah ok, so there is no violation to be caught because my table schema is this:
export const resources = pgTable("resources", {
id: varchar("id", { length: 191 })
.primaryKey()
.$defaultFn(() => nanoid()),
content: text("content").notNull(),
createdAt: timestamp("created_at")
.notNull()
.default(sqlnow()),
updatedAt: timestamp("updated_at")
.notNull()
.default(sqlnow()),
});Yes. You need your
content column to have UNIQUE constraint
Like this: content: text("content").unique().notNull()Ok ive updated the schema, managed the migrations, I think its now triggering, but the returning() part doesnt seem to work for the already existing row.