postgresqlHow do I use PostgreSQL's ON CONFLICT clause?
The ON CONFLICT
clause is a PostgreSQL extension to the SQL standard's INSERT
statement. It allows the user to specify an alternative action to take when a unique constraint or primary key violation occurs.
Example code
INSERT INTO table_name (column_name1, column_name2)
VALUES (value1, value2)
ON CONFLICT (column_name1) DO UPDATE SET column_name2 = value2;
This example will insert a row with value1
and value2
into table_name
, unless a row with value1
already exists in the column_name1
column. In that case, the existing row will be updated with value2
.
The ON CONFLICT
clause can also take other actions, such as ignoring the row, or performing a custom function.
Code explanation
INSERT INTO table_name (column_name1, column_name2)
- Inserts a row into the specified table, with the specified columns.VALUES (value1, value2)
- Specifies the values to be inserted into the row.ON CONFLICT (column_name1) DO UPDATE SET column_name2 = value2;
- Specifies what action to take if a row withvalue1
already exists in thecolumn_name1
column.
Helpful links
More of Postgresql
- How do I use PostgreSQL ZonedDateTime to store date and time information?
- How can Zalando use PostgreSQL to improve its software development?
- How can I troubleshoot zero damaged pages in PostgreSQL?
- How can I use PostgreSQL and ZFS snapshots together?
- How can I integrate PostgreSQL with Yii2?
- How can I retrieve data from PostgreSQL for yesterday's date?
- How can I use PostgreSQL XML functions to manipulate XML data?
- How do I use PostgreSQL's XMLTABLE to parse XML data?
- How can I set a PostgreSQL interval to zero?
- How can I use PostgreSQL's "zero if null" feature?
See more codes...