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();

 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?

oke•11/3/24, 10:44 PM

Your column needs to have a UNIQUE constraint

oke•11/3/24, 10:45 PM

This 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!

HoaxKeyOP•11/3/24, 10:47 PM

ah ok, so there is no violation to be caught because my table schema is this:

now()now()

oke•11/3/24, 10:47 PM

Yes. You need your column to have UNIQUE constraint
Like this:

HoaxKeyOP•11/3/24, 10:58 PM

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.