PostgreSQL normally allows multiple SQL NULL values in a unique column. To diagnose an unexpected duplicate-key error, identify the exact index, inspect the values actually sent, and check whether its definition changes how nulls compare. Unique Indexes

This walkthrough is for someone debugging article inserts from a Mac using psql or node-postgres. It uses synthetic article rows and provides a diagnostic sequence: reproduce the default behavior, trace the failing rule, then choose the appropriate correction.

What does a nullable unique column actually allow?

A nullable column can contain NULL; a unique constraint determines which combinations may repeat. With ordinary UNIQUE (slug), repeated non-null slugs are rejected, while repeated nulls are allowed. With ordinary UNIQUE (section, slug), a null in either constrained column permits otherwise repeated combinations under that constraint. Other unique rules on the table still apply. Constraints

PostgreSQL 15 introduced NULLS NOT DISTINCT, which treats nulls as equal for uniqueness checking. For a composite key, the full combination must match: ('notes', NULL) conflicts with another ('notes', NULL), but not with ('guides', NULL). PostgreSQL 14 does not support that clause. Release 15, Unique Indexes

Actual rule or input Can a repeated value cause a duplicate-key error?
Ordinary UNIQUE (slug) with SQL NULL No, under this rule alone
UNIQUE NULLS NOT DISTINCT (slug) Yes, on PostgreSQL 15 or later
Ordinary UNIQUE (slug) with '' Yes; this is an empty string
Ordinary UNIQUE (slug) with 'NULL' Yes; this is text
Another unique key on the same row Yes, independently of the nullable slug

SQL NULL, the empty string, and the text 'NULL' are different inputs. PostgreSQL’s comparison documentation explicitly distinguishes null from ordinary text values; test nullness with IS NULL. Comparison Functions and Operators

Reproduce the default behavior in Mac Terminal

You need an existing PostgreSQL connection and the psql command available in Terminal. Using the connection settings you normally use, open a session. For example, if your local database is named postgres, run the following. The -d option selects the database. psql

psql -d postgres

Check the connected server’s version before choosing version-specific syntax. server_version reports the server version, which is the relevant boundary for NULLS NOT DISTINCT. Preset Options

SHOW server_version;

Run this complete baseline example once in a fresh session. Its ordinary unique constraint works on PostgreSQL 14 and later; it creates only a temporary demonstration table.

CREATE TEMP TABLE article_probe (
    article_id integer PRIMARY KEY,
    title text NOT NULL,
    slug text,
    CONSTRAINT article_probe_slug_key UNIQUE (slug)
);

INSERT INTO article_probe (article_id, title, slug)
VALUES
    (1, 'Paper shapes', NULL),
    (2, 'Color arrangements', NULL),
    (3, 'Desk sketches', ''),
    (4, 'Pattern notes', 'NULL');

SELECT article_id, title, slug,
       slug IS NULL AS is_sql_null,
       slug = '' AS is_empty_string
FROM article_probe
ORDER BY article_id;

All four inserts should succeed under the displayed definition. The first two rows have is_sql_null = true; the third has is_empty_string = true; the fourth contains literal text. A comparison involving null can itself return null, so use the explicit nullness column to interpret the results. Constraints, Comparison Functions and Operators

This baseline gives you a useful dividing line. If the application fails while this example succeeds, compare the application’s actual table definition and submitted values with the baseline. The presence of a nullable column alone does not identify the failing rule.

Find the exact rule behind the error

Start with SQLSTATE 23505, PostgreSQL’s code for unique_violation. Check the code rather than matching the wording of the error message. Then record the reported schema, table, constraint, and detail when available. PostgreSQL treats an index as a constraint for the error’s constraint-name field, even when it was created directly with CREATE UNIQUE INDEX. PostgreSQL Error Codes, Error and Notice Message Fields

In the same psql session as the baseline, inspect the indexes with this diagnostic query. The pg_indexes view exposes each index’s schema, name, and reconstructed definition. When adapting the query, match both the table and schema reported by the error. pg_indexes

SELECT schemaname, tablename, indexname, indexdef
FROM pg_indexes
WHERE tablename = 'article_probe'
ORDER BY schemaname, indexname;

Read every unique definition, beginning with the name from the error. A duplicate article_id violates the primary key even when slug is null. Primary keys require unique, non-null values; a nullable column elsewhere in the row does not relax that requirement. Constraints

Look for three changes from the baseline:

  • Null equality: NULLS NOT DISTINCT deliberately makes repeated null keys conflict.
  • Expressions: uniqueness may apply to a calculated value rather than the stored column.
  • Predicates: a partial index applies its rule only to rows satisfying its WHERE condition.

For an expression index, reproduce the indexed expression when investigating the conflict. For example, an index on lower(slug) can reject two non-null slugs differing only in case. An index on COALESCE(slug, '') maps both null and the empty string to the same indexed value; consequently, those inputs can collide. This follows from expression-index uniqueness and the documented behavior of COALESCE. Indexes on Expressions, Conditional Expressions

A partial unique index also needs careful reading. An index on section with WHERE slug IS NULL enforces one matching row per section among the rows with null slugs. It does not enforce uniqueness on slug itself. PostgreSQL documents partial unique indexes as enforcing uniqueness within the subset selected by their predicate. Partial Indexes

Check what node-postgres actually sends

node-postgres converts both JavaScript null and undefined query parameters to SQL null. Empty strings and the string 'NULL' remain ordinary text inputs. Also distinguish a supplied null parameter from an omitted SQL column: PostgreSQL fills an omitted column with its declared default, or null when no default exists. Queries, INSERT

The following complete program makes those differences visible. It requires Node.js, npm, and your existing PostgreSQL connection settings. In a working folder, install the dependency, save the program as diagnose-null.mjs, then run it. The client uses standard PostgreSQL connection environment variables. Connecting

npm install pg

import pg from 'pg';

const { Client } = pg;
const client = new Client();

async function main() {
  await client.connect();
  try {
    await client.query(`
      CREATE TEMP TABLE article_node_probe (
        title text NOT NULL,
        slug text DEFAULT 'untitled',
        CONSTRAINT article_node_probe_slug_key UNIQUE (slug)
      )
    `);

    const samples = [
      { title: 'Paper shapes', slug: null },
      { title: 'Color arrangements', slug: undefined },
      { title: 'Desk sketches', slug: '' },
      { title: 'More sketches', slug: '' },
      { title: 'Pattern notes', slug: 'NULL' },
      { title: 'More patterns', slug: 'NULL' },
    ];

    for (const sample of samples) {
      console.log('Input:', {
        title: sample.title,
        type: typeof sample.slug,
        value: sample.slug,
      });

      try {
        const result = await client.query(
          `INSERT INTO article_node_probe (title, slug)
           VALUES ($1, $2)
           RETURNING title, slug, slug IS NULL AS is_sql_null`,
          [sample.title, sample.slug],
        );
        console.log('Inserted:', result.rows[0]);
      } catch (error) {
        if (error.code !== '23505') throw error;
        console.log('Duplicate:', {
          code: error.code,
          schema: error.schema,
          table: error.table,
          constraint: error.constraint,
          detail: error.detail,
        });
      }
    }
  } finally {
    await client.end();
  }
}

main().catch((error) => {
  console.error(error);
  process.exitCode = 1;
});

PGDATABASE=postgres node diagnose-null.mjs

Under this program’s schema, the first two rows should store SQL null despite the declared default. The first empty string and first 'NULL' string should insert; their repeats should produce 23505. The program reports those expected conflicts and rethrows other errors. Its diagnostic property names match node-postgres’s DatabaseError fields. Queries, DatabaseError source

The database examples were inspected against the retrieved documentation, but were not executed against PostgreSQL during preparation.

Query nullable keys without missing the matching rows

A diagnostic lookup using slug = NULL will not find null slugs. Use IS NULL for a fixed null search, or IS NOT DISTINCT FROM when a value may be either null or non-null. The latter treats two nulls as equal for the comparison. Comparison Functions and Operators

This diagnostic query runs against the earlier article_probe table in the same session. It should return the two null-slug rows.

SELECT article_id, title, slug
FROM article_probe
WHERE slug IS NOT DISTINCT FROM NULL::text
ORDER BY article_id;

For a parameterized lookup, the corresponding predicate fragment is WHERE slug IS NOT DISTINCT FROM $1::text. Match every key component for a composite rule, and reproduce any indexed expression or partial-index predicate. Finding matching null rows establishes what is stored; whether they violate uniqueness still depends on the index definition.

Choose the correction that matches the intended rule

If articles may have any number of missing slugs, ordinary UNIQUE (slug) already expresses that requirement. Correct an accidental text value, an unintended expression, or the other duplicate key identified by the error. Changing null handling will not resolve a collision on a different key.

If the requirement is one null slug per section, PostgreSQL 15 or later can express it directly. This independent demonstration deliberately ends with an insert expected to produce 23505: the first row already occupies that section-and-null combination. Release 15, Unique Indexes

CREATE TEMP TABLE article_one_missing (
    section text NOT NULL,
    slug text,
    title text NOT NULL,
    CONSTRAINT article_one_missing_key
        UNIQUE NULLS NOT DISTINCT (section, slug)
);

INSERT INTO article_one_missing (section, slug, title)
VALUES ('notes', NULL, 'Paper shapes');

-- Expected: 23505 on article_one_missing_key.
INSERT INTO article_one_missing (section, slug, title)
VALUES ('notes', NULL, 'Color arrangements');

On PostgreSQL 14, use partial unique indexes for this particular two-column design. The following independent example implements the same rule because section is non-null and slug is the only nullable key component. This is a construction derived from PostgreSQL’s documented partial-index behavior. Partial Indexes

CREATE TEMP TABLE article_legacy (
    section text NOT NULL,
    slug text,
    title text NOT NULL
);

CREATE UNIQUE INDEX article_legacy_present_key
ON article_legacy (section, slug)
WHERE slug IS NOT NULL;

CREATE UNIQUE INDEX article_legacy_missing_key
ON article_legacy (section)
WHERE slug IS NULL;

INSERT INTO article_legacy (section, slug, title)
VALUES ('notes', NULL, 'Paper shapes');

-- Expected: 23505 on article_legacy_missing_key.
INSERT INTO article_legacy (section, slug, title)
VALUES ('notes', NULL, 'Color arrangements');

The first index prevents repeated populated slugs within a section. The second permits one missing slug per section. More nullable key components require additional consideration of their possible combinations; this two-index construction is specific to the stated schema.

Finally, choose conflict handling explicitly. ON CONFLICT DO NOTHING skips a conflicting insertion; it does not repair the underlying values. ON CONFLICT DO UPDATE requires a conflict target, whose columns, expressions, and applicable predicate must identify the intended rule. Neither action creates a null conflict where ordinary uniqueness allows repeated nulls. INSERT

A diagnosis is complete when you can connect the reported index name to its definition, reproduce the submitted key values, and explain the collision under that rule. Repeat that exact input after the correction, checking both the returned result and the stored rows so the intended article is accounted for.

Sources