In the case of a primary key conflict, the column values in the pseudo-table are referenced to overwrite the column values in the t1 table.
Postgresql insert into from select update#
In the DO UPDATE SET clause, you can use excluded to represent the pseudo-table composed of conflicting data. Or INSERT INTO t1 VALUES (0,2,2,2) ON CONFLICT (a) DO UPDATE SET b = excluded.b, c = excluded.c, d = excluded.d The following statement provides an example on this scenario: INSERT INTO t1 VALUES (0,2,2,2) ON CONFLICT (a) DO UPDATE SET (b, c, d) = (excluded.b, excluded.c, excluded.d) This is applicable to scenarios where all columns in a table are overwritten. a | b | c | dĪdd the following ON CONFLICT DO UPDATE clause to update non-primary key columns. No operation is performed on the t1 table. The following statement provides an example on this scenario: INSERT INTO t1 VALUES (0,1,1,1) ON CONFLICT DO NOTHING This is applicable to scenarios where conflicting data is discarded. To prevent the preceding error message, you can use INSERT ON CONFLICT described in this topic.Īdd the following ON CONFLICT DO NOTHING clause to ignore the insertion of the conflicting data. If the following statement is executed to insert another row of data into the t1 table, and the inserted value of the a primary key is 0, an error is returned: INSERT INTO t1 VALUES (0,1,1,1) Ī similar error message is returned: ERROR: duplicate key value violates unique constraint "t1_pkey" The following result is returned: a | b | c | d Set four columns in the table and specify a as the primary key: CREATE TABLE t1 (a int PRIMARY KEY, b int, c int, d int DEFAULT 0) Įxecute the following statement to insert a row of data in which the value of the a primary key is 0: INSERT INTO t1 VALUES (0,0,0,0) Įxecute the following statement to query the t1 table: SELECT * FROM t1 Valid values:ĭO NOTHING: indicates that the data to insert is discarded if a data conflict occurs in columns specified by conflict_target.ĭO UPDATE: indicates that the data is overwritten based on the following UPDATE clause if a data conflict occurs in columns specified by conflict_target.Įxecute the following statement to create a table named t1. Specify the action to execute after a conflict. If conflick_action is set to Do Nothing, you can omit conflict_target. If conflict_action is set to Do Update, you must use conflict_target to specify the primary key or unique index column that is used to define a conflict. The following table describes the parameters. The clause consists of the conflict_target and conflict_action parameters. The ON CONFLICT clause can be added to overwrite data. The overwrite syntax is based on the following INSERT statement: with_query ] This is a universal limit based on the standard SQL syntax.
Multiple data records for a primary key cannot be inserted in an INSERT statement. Subqueries cannot be executed in the UPDATE WHERE clause.
Postgresql insert into from select how to#
For more information about how to upgrade the minor version, see Upgrade the engine version.ĭistribution columns and primary key columns cannot be updated in the UPDATE SET clause. The table can be a partitioned table only when the minor version of the instance is 6.3.6.1 or later. The table cannot be a column store table because column store tables do not support unique indexes. The table whose data is to be overwritten must be a row store table. AnalyticDB for PostgreSQL V4.3 does not support this feature. Only AnalyticDB for PostgreSQL V6.0 supports the overwrite feature. This feature is supported in AnalyticDB for PostgreSQL V6.0 and not supported in AnalyticDB for PostgreSQL V4.3. It is similar to the REPLACE INTO statement of MySQL. This feature is also known as UPSERT or INSERT OVERWRITE. The INSERT ON CONFLICT statement allows you to update an existing row that contains a primary key when you execute the INSERT statement to insert a new row that contains the same primary key.
0 kommentar(er)
