Command guide

intermediate inserting MySQL MariaDB SQL Server PostgreSQL SQLite

Insert Rows and Silently Skip Duplicates

Insert rows without raising an error when a unique constraint is violated, using each engine's conflict-ignore syntax.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Add new subscribers but skip rows whose email already exists

The incoming batch contains id 3 ([email protected]) and id 4 ([email protected]). Alice's email already exists in the table, so that row is silently dropped. Carol's email is new, so id 4 is inserted. The final table has three rows: the original two plus Carol. The original Alice row (id 1) is unchanged — this is an ignore, not an update.

MySQL MariaDB

Engine-specific syntax

Source table data

Setup

CREATE TABLE subscribers (id INT, email VARCHAR(100), UNIQUE (email));

INSERT INTO
  subscribers
VALUES
  (1, '[email protected]'),
  (2, '[email protected]');

Validated query

SQL

INSERT IGNORE INTO subscribers (id, email)
VALUES
  (3, '[email protected]'),
  (4, '[email protected]');

SELECT
  id,
  email
FROM
  subscribers
ORDER BY
  id;

Expected result

id	email
1	[email protected]
2	[email protected]
4	[email protected]

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE subscribers (id INT, email VARCHAR(100), UNIQUE (email));

INSERT INTO
  subscribers
VALUES
  (1, '[email protected]'),
  (2, '[email protected]');

Validated query

SQL

INSERT INTO
  subscribers (id, email)
SELECT
  v.id,
  v.email
FROM
  (
    VALUES
      (3, '[email protected]'),
      (4, '[email protected]')
  ) AS v (id, email)
WHERE
  NOT EXISTS (
    SELECT
      1
    FROM
      subscribers s
    WHERE
      s.email = v.email
  );

SELECT
  id,
  email
FROM
  subscribers
ORDER BY
  id;

Expected result

id	email
1	[email protected]
2	[email protected]
4	[email protected]

PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE subscribers (id INT, email VARCHAR(100), UNIQUE (email));

INSERT INTO
  subscribers
VALUES
  (1, '[email protected]'),
  (2, '[email protected]');

Validated query

SQL

INSERT INTO
  subscribers (id, email)
VALUES
  (3, '[email protected]'),
  (4, '[email protected]') ON CONFLICT (email) DO NOTHING;

SELECT
  id,
  email
FROM
  subscribers
ORDER BY
  id;

Expected result

id	email
1	[email protected]
2	[email protected]
4	[email protected]

SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE subscribers (id INT, email VARCHAR(100), UNIQUE (email));

INSERT INTO
  subscribers
VALUES
  (1, '[email protected]'),
  (2, '[email protected]');

Validated query

SQL

INSERT
OR IGNORE INTO subscribers (id, email)
VALUES
  (3, '[email protected]'),
  (4, '[email protected]');

SELECT
  id,
  email
FROM
  subscribers
ORDER BY
  id;

Expected result

id	email
1	[email protected]
2	[email protected]
4	[email protected]

MySQL and MariaDB use `INSERT IGNORE INTO`. PostgreSQL uses `ON CONFLICT (email) DO NOTHING`. SQLite uses `INSERT OR IGNORE INTO`. SQL Server uses a `WHERE NOT EXISTS` subquery to pre-filter conflicting rows. All produce identical results.

Useful when

Where this command helps.

loading a batch of rows where some may already exist and duplicates should be silently dropped
importing data from an external feed without needing to pre-deduplicate it

Explanation

What the command is doing.

Each engine offers a way to insert rows and quietly discard any that would violate a UNIQUE or primary-key constraint, without aborting the entire statement. MySQL and MariaDB use INSERT IGNORE INTO, which silences constraint errors for affected rows. PostgreSQL uses ON CONFLICT DO NOTHING. SQLite uses INSERT OR IGNORE INTO. SQL Server has no direct keyword but achieves the same result by filtering the source with WHERE NOT EXISTS. Unlike an upsert, none of these forms update the existing row — they simply drop the conflicting input row.

Insert Rows and Silently Skip Duplicates

Add new subscribers but skip rows whose email already exists

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.