Onjsdev

Share


Add a record to a PostgreSQL table only if it does not already exist


By onjsdev

Feb 8th, 2024

To add a record to a PostgreSQL table only if it does not already exist, you can use the INSERT ... ON CONFLICT DO NOTHING or INSERT ... ON CONFLICT DO UPDATE syntax, depending on your requirements.

Here's an example using the ON CONFLICT DO NOTHING approach:

Example

Assuming you have a table named your_table with columns column1, column2, and column3, and you want to insert a new record only if a record with a specific condition does not already exist:

INSERT INTO your_table (column1, column2, column3)
VALUES ('value1', 'value2', 'value3')
ON CONFLICT (column1) DO NOTHING;

In this example, column1 is assumed to be a unique or primary key. The ON CONFLICT (column1) DO NOTHING clause ensures that if there is a conflict (i.e., a record with the same value in column1 already exists), the new record will not be inserted.

If you want to update certain columns of the existing record when a conflict occurs, you can use the ON CONFLICT (column1) DO UPDATE syntax:

INSERT INTO your_table (column1, column2, column3)
VALUES ('value1', 'value2', 'value3')
ON CONFLICT (column1) DO UPDATE SET column2 = 'new_value2', column3 = 'new_value3';

This example will update column2 and column3 of the existing record if there is a conflict.

Remember that, if you encounter the error there is no unique or exclusion constraint matching the ON CONFLICT specification, this means the specified conflict target does not correspond to a unique or exclusion constraint in the table. To resolve this issue, you need to add UNIQUE or PRIMARY KEY attributes your the specified conflict target column.

Thank you for reading.