Prevent concurrent check-then-insert duplicates with a unique constraint on the article’s logical key and INSERT ... ON CONFLICT. When you also need the existing row, use a retrieval strategy that matches your transaction isolation level.

This tutorial is for a developer working in macOS Terminal who needs a Node.js function that creates a synthetic article once and returns its row to concurrent callers. The examples preserve the first article’s title. They use the current PostgreSQL documentation and official node-postgres documentation.

Why a transaction alone does not prevent duplicates

Consider two calls trying to create an article with the slug paper-shapes. Each first checks whether that slug exists, then inserts when the query returns nothing. At PostgreSQL’s default Read Committed isolation level, a normal query sees committed data as of its statement snapshot. Both checks can therefore report an absent article. PostgreSQL: Transaction Isolation

This illustrative schedule shows the application-level race:

Step Call A Call B
1 Checks paper-shapes: absent
2 Checks paper-shapes: absent
3 Inserts the article
4 Attempts to insert the same slug
5 Commits Attempts to commit

Without a unique constraint on the slug, those inserts can create separate rows. A primary key on an automatically generated ID does not express the rule that slugs must be unique. PostgreSQL supports unique constraints on either one column or a combination of columns. PostgreSQL: Constraints

Wrapping the check and insert in BEGIN and COMMIT does not reserve the absent slug. Adding FOR UPDATE to an empty lookup does not solve that problem either: it locks retrieved rows, so an absent article provides no row to lock. This follows from PostgreSQL’s documented row-lock behavior. PostgreSQL: Explicit Locking

Define exactly what counts as a duplicate

For this example, one exact slug identifies one article. The title is content, so a second request with the same slug should receive the stored article rather than replace its title.

Start with an existing local PostgreSQL installation, its command-line tools, and Node.js with npm. In macOS Terminal, create a fresh demonstration database and open its SQL prompt:

createdb article_concurrency_demo
psql -d article_concurrency_demo

Run this schema once in the new database:

CREATE TABLE articles (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    slug text NOT NULL,
    title text NOT NULL,
    CONSTRAINT articles_slug_key UNIQUE (slug)
);

The separate UNIQUE (slug) constraint enforces the article rule. NOT NULL also matters: ordinary unique constraints permit multiple null values by default. PostgreSQL automatically creates a unique index for this constraint. PostgreSQL: Constraints

If adapting an existing table, first inspect repeated keys rather than automatically deleting rows. This diagnostic query uses the schema above and leaves all content intact:

SELECT slug, count(*) AS row_count
FROM articles
GROUP BY slug
HAVING count(*) > 1;

Decide which stored content to retain before adding the constraint to an existing table. Also settle the intended key: if two articles may share a slug in different collections, the rule would need a composite key such as collection plus slug. Keep the lookup and conflict target aligned with that same rule.

Choose the required conflict behavior

A conflict is a normal branch of this task. Choose its meaning before choosing the SQL. PostgreSQL supports skipping the conflicting insertion or updating the existing row; RETURNING returns rows actually inserted or updated. PostgreSQL: INSERT

Required outcome Approach Returned result
Create if absent; otherwise skip ON CONFLICT (slug) DO NOTHING With RETURNING, a skipped insertion returns no row
Create if absent; otherwise replace the title ON CONFLICT (slug) DO UPDATE With RETURNING, the inserted or updated row
Create if absent; otherwise preserve and retrieve DO NOTHING, then a separate lookup Implemented in the next section

For an insert-only operation, this complete SQL statement works against the demonstration schema:

INSERT INTO articles (slug, title)
VALUES ('paper-shapes', 'Paper Shapes')
ON CONFLICT (slug) DO NOTHING
RETURNING id, slug, title;

If replacing the title is the intended behavior, use this alternative statement:

INSERT INTO articles (slug, title)
VALUES ('paper-shapes', 'Paper Shapes — Revised')
ON CONFLICT (slug) DO UPDATE
SET title = EXCLUDED.title
RETURNING id, slug, title;

Here, EXCLUDED.title refers to the proposed title. An unconditional DO UPDATE provides the documented atomic insert-or-update outcome, subject to independent errors and isolation-level failure handling. A conditional update whose WHERE condition rejects the update may return nothing. Choose an update because changing the stored article is appropriate. PostgreSQL: INSERT

Implement create-or-return-existing at Read Committed

There is a visibility trap: DO NOTHING can skip insertion because of a concurrent row invisible to the statement’s snapshot. Consequently, a same-statement lookup fallback can also miss it. A separate Read Committed query gets a new snapshot. This is the basis for the implementation below. PostgreSQL: Transaction Isolation

Exit the SQL prompt with \q. In a new working folder, install the Node.js dependency:

mkdir article-concurrency-demo
cd article-concurrency-demo
npm init -y
npm install pg

Save the following complete program as demo.mjs. It assumes the database and table created above. Its pool uses the named database and the remaining standard connection settings; node-postgres supports PostgreSQL connection environment variables when local settings differ. node-postgres: Connecting

import pg from 'pg';
import assert from 'node:assert/strict';

const { Pool } = pg;
const pool = new Pool({
  database: 'article_concurrency_demo',
  max: 8
});

async function getOrCreateArticle(slug, title) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN ISOLATION LEVEL READ COMMITTED');

    for (let attempt = 0; attempt < 5; attempt += 1) {
      const inserted = await client.query(`
        INSERT INTO articles (slug, title)
        VALUES ($1, $2)
        ON CONFLICT (slug) DO NOTHING
        RETURNING id, slug, title
      `, [slug, title]);

      if (inserted.rows.length === 1) {
        await client.query('COMMIT');
        return inserted.rows[0];
      }

      const existing = await client.query(`
        SELECT id, slug, title
        FROM articles
        WHERE slug = $1
        FOR UPDATE
      `, [slug]);

      if (existing.rows.length === 1) {
        await client.query('COMMIT');
        return existing.rows[0];
      }
    }

    throw new Error('Article kept disappearing; retry limit reached');
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}

try {
  const results = await Promise.allSettled(
    Array.from({ length: 8 }, () =>
      getOrCreateArticle('paper-shapes', 'Paper Shapes'))
  );

  const failures = results.filter(result => result.status === 'rejected');
  if (failures.length) {
    throw new AggregateError(failures.map(result => result.reason));
  }

  const rows = results.map(result => result.value);
  assert.equal(new Set(rows.map(row => row.id)).size, 1);

  const count = await pool.query(
    'SELECT count(*)::integer AS n FROM articles WHERE slug = $1',
    ['paper-shapes']
  );
  assert.equal(count.rows[0].n, 1);
  console.log({ calls: rows.length, matchingRows: count.rows[0].n });
} finally {
  await pool.end();
}

Run it from that folder:

node demo.mjs

With the stated setup and no other process changing the demonstration rows, the expected final output is:

{ calls: 8, matchingRows: 1 }

The program’s JavaScript syntax was checked with Node.js. The database demonstration was not executed here because PostgreSQL tooling was unavailable; the output above is an expected result.

Each transaction uses one checked-out client for every statement. node-postgres explicitly requires this: issuing transaction statements through pool.query can dispatch them to different clients. The verification query uses pool.query only after the transactions finish. node-postgres: Transactions

The query text and values are passed separately using $1 and $2, following the driver’s parameterized-query interface. node-postgres: Queries

The loop handles an additional illustrative interleaving: another transaction could remove the conflicting article before the lookup retrieves it. A missing lookup repeats the insertion attempt, with five attempts as an explicit example limit. Exhaustion produces an error rather than an undefined row.

Once retrieved, FOR UPDATE keeps the row from being changed or deleted by another transaction until this transaction ends. It protects an existing result; the unique constraint handles competing insertions. After commit, another transaction can change the row again. If dependent database work must use the locked article, place that work before COMMIT. PostgreSQL: Explicit Locking

Preserve the isolation-level limits

The example explicitly selects Read Committed. Repeatable Read uses a transaction snapshot established by its first substantive statement, so another query cannot simply refresh it. Serializable provides serial-equivalent committed outcomes, but can abort transactions and can still produce unique violations in documented overlapping cases. MERGE also lacks the same concurrent insert-or-update guarantee. PostgreSQL: Transaction Isolation

For Repeatable Read or Serializable, prepare to retry the complete transaction after SQLSTATE 40001, including the application decisions that produced its statements. Retrying only the last query is insufficient. PostgreSQL also identifies 40P01 as a potentially retryable deadlock failure. PostgreSQL: Serialization Failure Handling

Do not automatically treat every 23505 unique violation as transient. It may represent a persistent conflict. A retry policy should identify the expected condition, bound attempts, and report exhaustion; PostgreSQL does not guarantee that retries eventually succeed. The demonstration handles its expected slug collision through ON CONFLICT, while other errors propagate. PostgreSQL: Serialization Failure Handling

For this create-or-return task, the useful division is straightforward: the database constraint expresses article identity, the conflict action expresses what to do with an existing article, and the transaction controls retrieval and any dependent work. Keep those choices aligned, and concurrent callers can share one article without silently replacing its content.

Sources