Use On Conflict Do Nothing In Postgres Like Insert Or Ignore In Other Databases

March 2022

Postgres doesn't have "INSERT OR IGNORE"

Instead, use:

ON CONFLICT (column_name) DO NOTHING;

For example, given this table setup:

CREATE TABLE IF NOT EXISTS public.states (
    db_id SERIAL PRIMARY KEY,
    abbreviation VARCHAR(2) NOT NULL UNIQUE,
    full_name VARCHAR(30)
);

ALTER TABLE public.states ENABLE ROW LEVEL SECURITY;

This INSERT statement can be run multiple times:

INSERT INTO public.states (abbreviation, full_name)
VALUES ('AL', 'Alabama')
ON CONFLICT (abbreviation) DO NOTHING;
;

The first time will insert the data. Subsequent runs will be skipped without throwing errors.

end of line