Support for INSERT ON CONFLICT Clause

Availability

This feature is available since MogDB 5.0.6.

Introduction

MogDB supports the syntax and functionality of INSERT ON CONFLICT DO UPDATE/DO NOTHING. The ON CONFLICT clause specifies that when a unique constraint conflict arises, the statement following ON CONFLICT is executed, changing the INSERT action to UPDATE or DO NOTHING to avoid errors.

Benefits

It is compatible with the syntax newly added in PostgreSQL 9.5, reducing the migration cost for applications.

Syntax Description

The INSERT statement has a new ON CONFLICT clause:

[ WITH [ RECURSIVE ] with_query [, ...] ]   
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
[ ON CONFLICT [conflict_target] DO { NOTHING | { UPDATE SET column_name = { expression | DEFAULT } } [, ...] [ WHERE condition ] } ]

Where conflict_target can be one of the following:

( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
    ON CONSTRAINT constraint_name

The optional ON CONFLICT clause provides a method for handling insert conflicts, mainly used to resolve insert failures caused by unique constraints or primary key constraints. When attempting to insert a row of data, if the unique constraint or primary key constraint already has the same value of data, the ON CONFLICT clause can specify the behavior when a conflict occurs, such as performing an update operation instead of inserting new data, or ignoring the conflict and taking no action.

• conflict_target

  By selecting an index, it specifies which conflicts the ON CONFLICT should take alternative actions for. It either performs unique index inference or explicitly names a constraint. For ON CONFLICT DO NOTHING, conflict_target is optional. When omitted, conflicts with all valid constraints (and unique indexes) are handled. For ON CONFLICT DO UPDATE, conflict_target must be provided.

• index_column_name

  The name of the index column.

• index_expression

  Similar to index_column_name, but used for expressions of columns that appear in the index (not simple columns).

• collation

  When specified, it requires the corresponding index_column_name or index_expression to match using a specific collation. It is usually omitted because collation usually does not affect whether a constraint is violated.

• opclass

  When specified, it requires the corresponding index_column_name or index_expression to match using a specific operator class. It is usually omitted.

• index_predicate

  Used to allow inference of partial unique indexes. Any index that satisfies this predicate (not necessarily a partial index) can be inferred.

• constraint_name

  Explicitly specifies an arbitrator constraint by name, instead of inferring a constraint or index.

• condition

  An expression that returns a Boolean value; only records for which this expression returns true will be updated.

Constraints

• The target table does not support external tables.
• The target table does not support views, which is inconsistent with PostgreSQL behavior.
• SQL Bypass is not supported when there is a query statement in the INSERT.
• Column-store tables are not supported.
• Applicable to A and PG compatible modes.
• The allow_concurrent_tuple_update parameter needs to be set to off for scenarios that require compatibility with PG.
• The lockwait_timeout needs to be set to 0 for scenarios that require compatibility with PG.

Example

drop index if exists i_upsert;
drop table if exists t_upsert cascade;

-- Create a table and insert data
CREATE TABLE t_upsert(
    id int ,
    name text,
    price numeric
);
insert into  t_upsert select generate_series,'test' || generate_series,generate_series*10 from generate_series(1,10);
select * from t_upsert order by 1 limit 10;

-- Create an index
create unique index i_upsert on t_upsert(id);
select a.relname,b.indnatts,b.indisusable,b.indisunique,b.indisprimary 
from pg_class a,pg_index b 
where a.oid = b.indexrelid and b.indrelid = (select oid from pg_class where relname = 't_upsert') order by 1;

-- Insert duplicate values, the conflict_target in the insert on conflict statement is the index column name
insert into  t_upsert values(3,'gram',5.5) on conflict(id) do update set name='gram';
select * from t_upsert order by 1;

-- Clean up data
drop index if exists i_upsert;
drop table if exists t_upsert cascade;

Related Pages

INSERT