{"Entry":{"collection":"sql","key":"insert","name":"INSERT","aliases":["insert"],"metadata":{"aliases":["insert"],"changed_in":["7.1","7.3","7.4","8.2","8.4","9.1","9.2","9.5","10","12","18","19"],"changes":[{"from":"6.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["usage","compatibility"],"removed":[]},"status":"changed","synopsis":null,"to":"6.5"},{"from":"6.5","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","usage","compatibility"],"removed":[]},"status":"changed","synopsis":null,"to":"7.0"},{"from":"7.0","purpose_changed":false,"renamed":{"from_file":"sql-insert.htm","to_file":"sql-insert.html"},"sections":{"added":[],"changed":["description","usage"],"removed":[]},"status":"changed","synopsis":{"added":["{ DEFAULT VALUES | VALUES ( expression [, ...] ) | SELECT query }"],"removed":[]},"to":"7.1"},{"from":"7.1","purpose_changed":true,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"7.2"},{"from":"7.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["usage"],"removed":[]},"status":"changed","synopsis":{"added":["{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | SELECT query }"],"removed":[]},"to":"7.3"},{"from":"7.3","purpose_changed":false,"renamed":null,"sections":{"added":["parameters","outputs","examples"],"changed":["description","compatibility"],"removed":["usage"]},"status":"changed","synopsis":{"added":[],"removed":["{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | SELECT query }"]},"to":"7.4"},{"from":"7.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","examples","compatibility"],"removed":[]},"status":"changed","synopsis":null,"to":"8.0"},{"from":"8.1","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","outputs","examples","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }","[ RETURNING * | output_expression [ AS output_name ] [, ...] ]"],"removed":[]},"to":"8.2"},{"from":"8.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"8.3"},{"from":"8.3","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":{"added":["[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]"],"removed":[]},"to":"8.4"},{"from":"9.0","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","examples","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["[ WITH [ RECURSIVE ] with_query [, ...] ]"],"removed":[]},"to":"9.1"},{"from":"9.1","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":{"added":["INSERT INTO table_name [ ( column_name [, ...] ) ]"],"removed":["INSERT INTO table [ ( column [, ...] ) ]"]},"to":"9.2"},{"from":"9.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","outputs","examples","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]","[ ON CONFLICT [ conflict_target ] conflict_action ]","[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]","( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]","ON CONSTRAINT constraint_name","DO NOTHING","DO UPDATE SET { column_name = { expression | DEFAULT } |","( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |","( column_name [, ...] ) = ( sub-SELECT )","} [, ...]","[ WHERE condition ]"],"removed":[]},"to":"9.5"},{"from":"9.6","purpose_changed":false,"renamed":null,"sections":{"added":["notes"],"changed":["description","parameters","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]","[ OVERRIDING { SYSTEM | USER } VALUE ]","( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |"],"removed":[]},"to":"10"},{"from":"10","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"11"},{"from":"11","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["outputs"],"removed":[]},"status":"changed","synopsis":{"added":["[ RETURNING { * | output_expression [ [ AS ] output_name ] } [, ...] ]"],"removed":[]},"to":"12"},{"from":"12","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"13"},{"from":"13","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"14"},{"from":"14","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","notes","compatibility"],"removed":[]},"status":"changed","synopsis":null,"to":"15"},{"from":"16","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"17"},{"from":"17","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","examples"],"removed":[]},"status":"changed","synopsis":{"added":["[ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ]"],"removed":[]},"to":"18"},{"from":"18","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","outputs","examples"],"removed":[]},"status":"changed","synopsis":{"added":["DO SELECT [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } ] [ WHERE condition ]","DO UPDATE SET { column_name = { expression | DEFAULT } |"],"removed":[]},"to":"19"}],"content_hash":"2e20d8d7b8b4d7a02e624f7a72ed0059650b3d5052d1eab2f8616cf6225ede99","editorial":{},"first_version":"6.4","group":"query","imported_at":"2026-09-27T17:57:27.040639+08:00","last_version":"20","name":"INSERT","object":"","position":11003,"present_in":["6.4","6.5","7.0","7.1","7.2","7.3","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6","10","11","12","13","14","15","16","17","18","19","20"],"purpose":"create new rows in a table","purpose_zh":"","related":[],"slug":"insert","source_rev":"b7bd9cda","synopsis":"[ WITH [ RECURSIVE ] with_query [, ...] ]\nINSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]\n[ OVERRIDING { SYSTEM | USER } VALUE ]\n{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }\n[ ON CONFLICT [ conflict_target ] conflict_action ]\n[ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ]\n{ * | output_expression [ [ AS ] output_name ] } [, ...] ]\n\nwhere conflict_target can be one of:\n\n( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]\nON CONSTRAINT constraint_name\nand conflict_action is one of:\n\nDO NOTHING\nDO SELECT [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } ] [ WHERE condition ]\nDO UPDATE SET { column_name = { expression | DEFAULT } |\n( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |\n( column_name [, ...] ) = ( sub-SELECT )\n} [, ...]\n[ WHERE condition ]","verb":"INSERT"}},"Definition":{"Collection":"sql","Key":"insert","SourceDatabase":"center","Version":"18","SourceTable":"sqlcmd","SourceKey":"insert","SourceRevision":"b7bd9cda","Facts":{"anchor":"SQL-INSERT","file":"sql-insert.html","lang":"en","name":"INSERT","purpose":"create new rows in a table","purpose_zh":"","related":[],"sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e inserts new rows into a table. One can insert one or more rows specified by value expressions, or zero or more rows resulting from a query.\u003c/p\u003e\u003cp\u003eThe target column names can be listed in any order. If no list of column names is given at all, the default is all the columns of the table in their declared order; or the first \u003cem class=\"replaceable\"\u003e\u003ccode\u003eN\u003c/code\u003e\u003c/em\u003e column names, if there are only \u003cem class=\"replaceable\"\u003e\u003ccode\u003eN\u003c/code\u003e\u003c/em\u003e columns supplied by the \u003ccode class=\"literal\"\u003eVALUES\u003c/code\u003e clause or \u003cem class=\"replaceable\"\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e. The values supplied by the \u003ccode class=\"literal\"\u003eVALUES\u003c/code\u003e clause or \u003cem class=\"replaceable\"\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e are associated with the explicit or implicit column list left-to-right.\u003c/p\u003e\u003cp\u003eEach column not present in the explicit or implicit column list will be filled with a default value, either its declared default value or null if there is none.\u003c/p\u003e\u003cp\u003eIf the expression for any column is not of the correct data type, automatic type conversion will be attempted.\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e into tables that lack unique indexes will not be blocked by concurrent activity. Tables with unique indexes might block if concurrent sessions perform actions that lock or modify rows matching the unique index values being inserted; the details are covered in \u003ca href=\"/docs/18/index-unique-checks.html\" title=\"63.5. Index Uniqueness Checks\"\u003eSection 63.5\u003c/a\u003e. \u003ccode class=\"literal\"\u003eON CONFLICT\u003c/code\u003e can be used to specify an alternative action to raising a unique constraint or exclusion constraint violation error. (See \u003ca href=\"/docs/18/sql-insert.html#SQL-ON-CONFLICT\" title=\"ON CONFLICT Clause\"\u003eON CONFLICT Clause\u003c/a\u003e below.)\u003c/p\u003e\u003cp\u003eThe optional \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e clause causes \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e to compute and return value(s) based on each row actually inserted (or updated, if an \u003ccode class=\"literal\"\u003eON CONFLICT DO UPDATE\u003c/code\u003e clause was used). This is primarily useful for obtaining values that were supplied by defaults, such as a serial sequence number. However, any expression using the table's columns is allowed. The syntax of the \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e list is identical to that of the output list of \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e. Only rows that were successfully inserted or updated will be returned. For example, if a row was locked but not updated because an \u003ccode class=\"literal\"\u003eON CONFLICT DO UPDATE ... WHERE\u003c/code\u003e clause \u003cem class=\"replaceable\"\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e was not satisfied, the row will not be returned.\u003c/p\u003e\u003cp\u003eYou must have \u003ccode class=\"literal\"\u003eINSERT\u003c/code\u003e privilege on a table in order to insert into it. If \u003ccode class=\"literal\"\u003eON CONFLICT DO UPDATE\u003c/code\u003e is present, \u003ccode class=\"literal\"\u003eUPDATE\u003c/code\u003e privilege on the table is also required.\u003c/p\u003e\u003cp\u003eIf a column list is specified, you only need \u003ccode class=\"literal\"\u003eINSERT\u003c/code\u003e privilege on the listed columns. Similarly, when \u003ccode class=\"literal\"\u003eON CONFLICT DO UPDATE\u003c/code\u003e is specified, you only need \u003ccode class=\"literal\"\u003eUPDATE\u003c/code\u003e privilege on the column(s) that are listed to be updated. However, all forms of \u003ccode class=\"literal\"\u003eON CONFLICT\u003c/code\u003e also require \u003ccode class=\"literal\"\u003eSELECT\u003c/code\u003e privilege on any column whose values are read. This includes any column mentioned in \u003cem class=\"replaceable\"\u003e\u003ccode\u003econflict_target\u003c/code\u003e\u003c/em\u003e (including columns referred to by the arbiter constraint), and any column mentioned in an \u003ccode class=\"literal\"\u003eON CONFLICT DO UPDATE\u003c/code\u003e \u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e, or a \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e clause \u003cem class=\"replaceable\"\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e.\u003c/p\u003e\u003cp\u003eUse of the \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e clause requires \u003ccode class=\"literal\"\u003eSELECT\u003c/code\u003e privilege on all columns mentioned in \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e. If you use the \u003cem class=\"replaceable\"\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e clause to insert rows from a query, you of course need to have \u003ccode class=\"literal\"\u003eSELECT\u003c/code\u003e privilege on any table or column used in the query.\u003c/p\u003e","key":"description","title":"Description"},{"html":"\u003cdiv class=\"refsect2\"\u003e\u003ch3\u003eInserting\u003c/h3\u003e\u003cp\u003eThis section covers parameters that may be used when only inserting new rows. Parameters \u003cspan class=\"emphasis\"\u003e\u003cem\u003eexclusively\u003c/em\u003e\u003c/span\u003e used with the \u003ccode class=\"literal\"\u003eON CONFLICT\u003c/code\u003e clause are described separately.\u003c/p\u003e\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ewith_query\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe \u003ccode class=\"literal\"\u003eWITH\u003c/code\u003e clause allows you to specify one or more subqueries that can be referenced by name in the \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e query. See \u003ca href=\"/docs/18/queries-with.html\" title=\"7.8. WITH Queries (Common Table Expressions)\"\u003eSection 7.8\u003c/a\u003e and \u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\"\u003e\u003cspan class=\"refentrytitle\"\u003eSELECT\u003c/span\u003e\u003c/a\u003e for details.\u003c/p\u003e\u003cp\u003eIt is possible for the \u003cem class=\"replaceable\"\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e (\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e statement) to also contain a \u003ccode class=\"literal\"\u003eWITH\u003c/code\u003e clause. In such a case both sets of \u003cem class=\"replaceable\"\u003e\u003ccode\u003ewith_query\u003c/code\u003e\u003c/em\u003e can be referenced within the \u003cem class=\"replaceable\"\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e, but the second one takes precedence since it is more closely nested.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name (optionally schema-qualified) of an existing table.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ealias\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eA substitute name for \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e. When an alias is provided, it completely hides the actual name of the table. This is particularly useful when \u003ccode class=\"literal\"\u003eON CONFLICT DO UPDATE\u003c/code\u003e targets a table named \u003ccode class=\"varname\"\u003eexcluded\u003c/code\u003e, since that will otherwise be taken as the name of the special table representing the row proposed for insertion.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name of a column in the table named by \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e. The column name can be qualified with a subfield name or array subscript, if needed. (Inserting into only some fields of a composite column leaves the other fields null.) When referencing a column with \u003ccode class=\"literal\"\u003eON CONFLICT DO UPDATE\u003c/code\u003e, do not include the table's name in the specification of a target column. For example, \u003ccode class=\"literal\"\u003eINSERT INTO table_name ... ON CONFLICT DO UPDATE SET table_name.col = 1\u003c/code\u003e is invalid (this follows the general behavior for \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e).\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eOVERRIDING SYSTEM VALUE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eIf this clause is specified, then any values supplied for identity columns will override the default sequence-generated values.\u003c/p\u003e\u003cp\u003eFor an identity column defined as \u003ccode class=\"literal\"\u003eGENERATED ALWAYS\u003c/code\u003e, it is an error to insert an explicit value (other than \u003ccode class=\"literal\"\u003eDEFAULT\u003c/code\u003e) without specifying either \u003ccode class=\"literal\"\u003eOVERRIDING SYSTEM VALUE\u003c/code\u003e or \u003ccode class=\"literal\"\u003eOVERRIDING USER VALUE\u003c/code\u003e. (For an identity column defined as \u003ccode class=\"literal\"\u003eGENERATED BY DEFAULT\u003c/code\u003e, \u003ccode class=\"literal\"\u003eOVERRIDING SYSTEM VALUE\u003c/code\u003e is the normal behavior and specifying it does nothing, but \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e allows it as an extension.)\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eOVERRIDING USER VALUE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eIf this clause is specified, then any values supplied for identity columns are ignored and the default sequence-generated values are applied.\u003c/p\u003e\u003cp\u003eThis clause is useful for example when copying values between tables. Writing \u003ccode class=\"literal\"\u003eINSERT INTO tbl2 OVERRIDING USER VALUE SELECT * FROM tbl1\u003c/code\u003e will copy from \u003ccode class=\"literal\"\u003etbl1\u003c/code\u003e all columns that are not identity columns in \u003ccode class=\"literal\"\u003etbl2\u003c/code\u003e while values for the identity columns in \u003ccode class=\"literal\"\u003etbl2\u003c/code\u003e will be generated by the sequences associated with \u003ccode class=\"literal\"\u003etbl2\u003c/code\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eDEFAULT VALUES\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eAll columns will be filled with their default values, as if \u003ccode class=\"literal\"\u003eDEFAULT\u003c/code\u003e were explicitly specified for each column. (An \u003ccode class=\"literal\"\u003eOVERRIDING\u003c/code\u003e clause is not permitted in this form.)\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eAn expression or value to assign to the corresponding column.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eDEFAULT\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe corresponding column will be filled with its default value. An identity column will be filled with a new value generated by the associated sequence. For a generated column, specifying this is permitted but merely specifies the normal behavior of computing the column from its generation expression.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eA query (\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e statement) that supplies the rows to be inserted. Refer to the \u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\"\u003e\u003cspan class=\"refentrytitle\"\u003eSELECT\u003c/span\u003e\u003c/a\u003e statement for a description of the syntax.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_alias\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eAn optional substitute name for \u003ccode class=\"literal\"\u003eOLD\u003c/code\u003e or \u003ccode class=\"literal\"\u003eNEW\u003c/code\u003e rows in the \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e list.\u003c/p\u003e\u003cp\u003eBy default, old values from the target table can be returned by writing \u003ccode class=\"literal\"\u003eOLD.\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e or \u003ccode class=\"literal\"\u003eOLD.*\u003c/code\u003e, and new values can be returned by writing \u003ccode class=\"literal\"\u003eNEW.\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e or \u003ccode class=\"literal\"\u003eNEW.*\u003c/code\u003e. When an alias is provided, these names are hidden and the old or new rows must be referred to using the alias. For example \u003ccode class=\"literal\"\u003eRETURNING WITH (OLD AS o, NEW AS n) o.*, n.*\u003c/code\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_expression\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eAn expression to be computed and returned by the \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e command after each row is inserted or updated. The expression can use any column names of the table named by \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e. Write \u003ccode class=\"literal\"\u003e*\u003c/code\u003e to return all columns of the inserted or updated row(s).\u003c/p\u003e\u003cp\u003eA column name or \u003ccode class=\"literal\"\u003e*\u003c/code\u003e may be qualified using \u003ccode class=\"literal\"\u003eOLD\u003c/code\u003e or \u003ccode class=\"literal\"\u003eNEW\u003c/code\u003e, or the corresponding \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_alias\u003c/code\u003e\u003c/em\u003e for \u003ccode class=\"literal\"\u003eOLD\u003c/code\u003e or \u003ccode class=\"literal\"\u003eNEW\u003c/code\u003e, to cause old or new values to be returned. An unqualified column name, or \u003ccode class=\"literal\"\u003e*\u003c/code\u003e, or a column name or \u003ccode class=\"literal\"\u003e*\u003c/code\u003e qualified using the target table name or alias will return new values.\u003c/p\u003e\u003cp\u003eFor a simple \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e, all old values will be \u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e. However, for an \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e with an \u003ccode class=\"literal\"\u003eON CONFLICT DO UPDATE\u003c/code\u003e clause, the old values may be non-\u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eA name to use for a returned column.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/div\u003e\u003cdiv class=\"refsect2\"\u003e\u003ch3\u003e\u003ccode class=\"literal\"\u003eON CONFLICT\u003c/code\u003e Clause\u003c/h3\u003e\u003cp\u003eThe optional \u003ccode class=\"literal\"\u003eON CONFLICT\u003c/code\u003e clause specifies an alternative action to raising a unique violation or exclusion constraint violation error. For each individual row proposed for insertion, either the insertion proceeds, or, if an \u003cspan class=\"emphasis\"\u003e\u003cem\u003earbiter\u003c/em\u003e\u003c/span\u003e constraint or index specified by \u003cem class=\"parameter\"\u003e\u003ccode\u003econflict_target\u003c/code\u003e\u003c/em\u003e is violated, the alternative \u003cem class=\"parameter\"\u003e\u003ccode\u003econflict_action\u003c/code\u003e\u003c/em\u003e is taken. \u003ccode class=\"literal\"\u003eON CONFLICT DO NOTHING\u003c/code\u003e simply avoids inserting a row as its alternative action. \u003ccode class=\"literal\"\u003eON CONFLICT DO UPDATE\u003c/code\u003e updates the existing row that conflicts with the row proposed for insertion as its alternative action.\u003c/p\u003e\u003cp\u003e\u003cem class=\"parameter\"\u003e\u003ccode\u003econflict_target\u003c/code\u003e\u003c/em\u003e can perform \u003cspan class=\"emphasis\"\u003e\u003cem\u003eunique index inference\u003c/em\u003e\u003c/span\u003e. When performing inference, it consists of one or more \u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_column_name\u003c/code\u003e\u003c/em\u003e columns and/or \u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_expression\u003c/code\u003e\u003c/em\u003e expressions, and an optional \u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_predicate\u003c/code\u003e\u003c/em\u003e. All \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e unique indexes that, without regard to order, contain exactly the \u003cem class=\"parameter\"\u003e\u003ccode\u003econflict_target\u003c/code\u003e\u003c/em\u003e-specified columns/expressions are inferred (chosen) as arbiter indexes. If an \u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_predicate\u003c/code\u003e\u003c/em\u003e is specified, it must, as a further requirement for inference, satisfy arbiter indexes. Note that this means a non-partial unique index (a unique index without a predicate) will be inferred (and thus used by \u003ccode class=\"literal\"\u003eON CONFLICT\u003c/code\u003e) if such an index satisfying every other criteria is available. If an attempt at inference is unsuccessful, an error is raised.\u003c/p\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003eON CONFLICT DO UPDATE\u003c/code\u003e guarantees an atomic \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e or \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e outcome; provided there is no independent error, one of those two outcomes is guaranteed, even under high concurrency. This is also known as \u003cem class=\"firstterm\"\u003eUPSERT\u003c/em\u003e — \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003eUPDATE or INSERT\u003c/span\u003e”\u003c/span\u003e.\u003c/p\u003e\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003econflict_target\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eSpecifies which conflicts \u003ccode class=\"literal\"\u003eON CONFLICT\u003c/code\u003e takes the alternative action on by choosing \u003cem class=\"firstterm\"\u003earbiter indexes\u003c/em\u003e. Either performs \u003cspan class=\"emphasis\"\u003e\u003cem\u003eunique index inference\u003c/em\u003e\u003c/span\u003e, or names a constraint explicitly. For \u003ccode class=\"literal\"\u003eON CONFLICT DO NOTHING\u003c/code\u003e, it is optional to specify a \u003cem class=\"parameter\"\u003e\u003ccode\u003econflict_target\u003c/code\u003e\u003c/em\u003e; when omitted, conflicts with all usable constraints (and unique indexes) are handled. For \u003ccode class=\"literal\"\u003eON CONFLICT DO UPDATE\u003c/code\u003e, a \u003cem class=\"parameter\"\u003e\u003ccode\u003econflict_target\u003c/code\u003e\u003c/em\u003e \u003cspan class=\"emphasis\"\u003e\u003cem\u003emust\u003c/em\u003e\u003c/span\u003e be provided.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003econflict_action\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003cem class=\"parameter\"\u003e\u003ccode\u003econflict_action\u003c/code\u003e\u003c/em\u003e specifies an alternative \u003ccode class=\"literal\"\u003eON CONFLICT\u003c/code\u003e action. It can be either \u003ccode class=\"literal\"\u003eDO NOTHING\u003c/code\u003e, or a \u003ccode class=\"literal\"\u003eDO UPDATE\u003c/code\u003e clause specifying the exact details of the \u003ccode class=\"literal\"\u003eUPDATE\u003c/code\u003e action to be performed in case of a conflict. The \u003ccode class=\"literal\"\u003eSET\u003c/code\u003e and \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e clauses in \u003ccode class=\"literal\"\u003eON CONFLICT DO UPDATE\u003c/code\u003e have access to the existing row using the table's name (or an alias), and to the row proposed for insertion using the special \u003ccode class=\"varname\"\u003eexcluded\u003c/code\u003e table. \u003ccode class=\"literal\"\u003eSELECT\u003c/code\u003e privilege is required on any column in the target table where corresponding \u003ccode class=\"varname\"\u003eexcluded\u003c/code\u003e columns are read.\u003c/p\u003e\u003cp\u003eNote that the effects of all per-row \u003ccode class=\"literal\"\u003eBEFORE INSERT\u003c/code\u003e triggers are reflected in \u003ccode class=\"varname\"\u003eexcluded\u003c/code\u003e values, since those effects may have contributed to the row being excluded from insertion.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_column_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name of a \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e column. Used to infer arbiter indexes. Follows \u003ccode class=\"command\"\u003eCREATE INDEX\u003c/code\u003e format. \u003ccode class=\"literal\"\u003eSELECT\u003c/code\u003e privilege on \u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_column_name\u003c/code\u003e\u003c/em\u003e is required.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_expression\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eSimilar to \u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_column_name\u003c/code\u003e\u003c/em\u003e, but used to infer expressions on \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e columns appearing within index definitions (not simple columns). Follows \u003ccode class=\"command\"\u003eCREATE INDEX\u003c/code\u003e format. \u003ccode class=\"literal\"\u003eSELECT\u003c/code\u003e privilege on any column appearing within \u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_expression\u003c/code\u003e\u003c/em\u003e is required.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecollation\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eWhen specified, mandates that corresponding \u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_column_name\u003c/code\u003e\u003c/em\u003e or \u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_expression\u003c/code\u003e\u003c/em\u003e use a particular collation in order to be matched during inference. Typically this is omitted, as collations usually do not affect whether or not a constraint violation occurs. Follows \u003ccode class=\"command\"\u003eCREATE INDEX\u003c/code\u003e format.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eopclass\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eWhen specified, mandates that corresponding \u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_column_name\u003c/code\u003e\u003c/em\u003e or \u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_expression\u003c/code\u003e\u003c/em\u003e use particular operator class in order to be matched during inference. Typically this is omitted, as the \u003cspan class=\"emphasis\"\u003e\u003cem\u003eequality\u003c/em\u003e\u003c/span\u003e semantics are often equivalent across a type's operator classes anyway, or because it's sufficient to trust that the defined unique indexes have the pertinent definition of equality. Follows \u003ccode class=\"command\"\u003eCREATE INDEX\u003c/code\u003e format.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_predicate\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eUsed to allow inference of partial unique indexes. Any indexes that satisfy the predicate (which need not actually be partial indexes) can be inferred. Follows \u003ccode class=\"command\"\u003eCREATE INDEX\u003c/code\u003e format. \u003ccode class=\"literal\"\u003eSELECT\u003c/code\u003e privilege on any column appearing within \u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_predicate\u003c/code\u003e\u003c/em\u003e is required.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003econstraint_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eExplicitly specifies an arbiter \u003cspan class=\"emphasis\"\u003e\u003cem\u003econstraint\u003c/em\u003e\u003c/span\u003e by name, rather than inferring a constraint or index.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eAn expression that returns a value of type \u003ccode class=\"type\"\u003eboolean\u003c/code\u003e. Only rows for which this expression returns \u003ccode class=\"literal\"\u003etrue\u003c/code\u003e will be updated, although all rows will be locked when the \u003ccode class=\"literal\"\u003eON CONFLICT DO UPDATE\u003c/code\u003e action is taken. Note that \u003cem class=\"replaceable\"\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e is evaluated last, after a conflict has been identified as a candidate to update.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003cp\u003eNote that exclusion constraints are not supported as arbiters with \u003ccode class=\"literal\"\u003eON CONFLICT DO UPDATE\u003c/code\u003e. In all cases, only \u003ccode class=\"literal\"\u003eNOT DEFERRABLE\u003c/code\u003e constraints and unique indexes are supported as arbiters.\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e with an \u003ccode class=\"literal\"\u003eON CONFLICT DO UPDATE\u003c/code\u003e clause is a \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003edeterministic\u003c/span\u003e”\u003c/span\u003e statement. This means that the command will not be allowed to affect any single existing row more than once; a cardinality violation error will be raised when this situation arises. Rows proposed for insertion should not duplicate each other in terms of attributes constrained by an arbiter index or constraint.\u003c/p\u003e\u003cp\u003eNote that it is currently not supported for the \u003ccode class=\"literal\"\u003eON CONFLICT DO UPDATE\u003c/code\u003e clause of an \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e applied to a partitioned table to update the partition key of a conflicting row such that it requires the row be moved to a new partition.\u003c/p\u003e\u003cdiv class=\"tip\"\u003e\u003ch3\u003eTip\u003c/h3\u003e\u003cp\u003eIt is often preferable to use unique index inference rather than naming a constraint directly using \u003ccode class=\"literal\"\u003eON CONFLICT ON CONSTRAINT\u003c/code\u003e \u003cem class=\"replaceable\"\u003e\u003ccode\u003econstraint_name\u003c/code\u003e\u003c/em\u003e. Inference will continue to work correctly when the underlying index is replaced by another more or less equivalent index in an overlapping way, for example when using \u003ccode class=\"literal\"\u003eCREATE UNIQUE INDEX ... CONCURRENTLY\u003c/code\u003e before dropping the index being replaced.\u003c/p\u003e\u003c/div\u003e\u003cdiv class=\"warning\"\u003e\u003ch3\u003eWarning\u003c/h3\u003e\u003cp\u003eWhile \u003ccode class=\"command\"\u003eCREATE INDEX CONCURRENTLY\u003c/code\u003e or \u003ccode class=\"command\"\u003eREINDEX CONCURRENTLY\u003c/code\u003e is running on a unique index, \u003ccode class=\"command\"\u003eINSERT ... ON CONFLICT\u003c/code\u003e statements on the same table may unexpectedly fail with a unique violation.\u003c/p\u003e\u003c/div\u003e\u003c/div\u003e","key":"parameters","title":"Parameters"},{"html":"\u003cp\u003eOn successful completion, an \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e command returns a command tag of the form\u003c/p\u003e\u003cpre class=\"screen\"\u003eINSERT \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoid\u003c/code\u003e\u003c/em\u003e \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\n\u003c/pre\u003e\u003cp\u003eThe \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e is the number of rows inserted or updated. \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoid\u003c/code\u003e\u003c/em\u003e is always 0 (it used to be the \u003cacronym\u003eOID\u003c/acronym\u003e assigned to the inserted row if \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e was exactly one and the target table was declared \u003ccode class=\"literal\"\u003eWITH OIDS\u003c/code\u003e and 0 otherwise, but creating a table \u003ccode class=\"literal\"\u003eWITH OIDS\u003c/code\u003e is not supported anymore).\u003c/p\u003e\u003cp\u003eIf the \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e command contains a \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e clause, the result will be similar to that of a \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e statement containing the columns and values defined in the \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e list, computed over the row(s) inserted or updated by the command.\u003c/p\u003e","key":"outputs","title":"Outputs"},{"html":"\u003cp\u003eIf the specified table is a partitioned table, each row is routed to the appropriate partition and inserted into it. If the specified table is a partition, an error will occur if one of the input rows violates the partition constraint.\u003c/p\u003e\u003cp\u003eYou may also wish to consider using \u003ccode class=\"command\"\u003eMERGE\u003c/code\u003e, since that allows mixing \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e, \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e, and \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e within a single statement. See \u003ca href=\"/docs/18/sql-merge.html\" title=\"MERGE\"\u003e\u003cspan class=\"refentrytitle\"\u003eMERGE\u003c/span\u003e\u003c/a\u003e.\u003c/p\u003e","key":"notes","title":"Notes"},{"html":"\u003cp\u003eInsert a single row into table \u003ccode class=\"literal\"\u003efilms\u003c/code\u003e:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eINSERT INTO films VALUES\n    ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes');\n\u003c/pre\u003e\u003cp\u003eIn this example, the \u003ccode class=\"literal\"\u003elen\u003c/code\u003e column is omitted and therefore it will have the default value:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eINSERT INTO films (code, title, did, date_prod, kind)\n    VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');\n\u003c/pre\u003e\u003cp\u003eThis example uses the \u003ccode class=\"literal\"\u003eDEFAULT\u003c/code\u003e clause for the date columns rather than specifying a value:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eINSERT INTO films VALUES\n    ('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes');\nINSERT INTO films (code, title, did, date_prod, kind)\n    VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama');\n\u003c/pre\u003e\u003cp\u003eTo insert a row consisting entirely of default values:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eINSERT INTO films DEFAULT VALUES;\n\u003c/pre\u003e\u003cp\u003eTo insert multiple rows using the multirow \u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e syntax:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eINSERT INTO films (code, title, did, date_prod, kind) VALUES\n    ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),\n    ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');\n\u003c/pre\u003e\u003cp\u003eThis example inserts some rows into table \u003ccode class=\"literal\"\u003efilms\u003c/code\u003e from a table \u003ccode class=\"literal\"\u003etmp_films\u003c/code\u003e with the same column layout as \u003ccode class=\"literal\"\u003efilms\u003c/code\u003e:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eINSERT INTO films SELECT * FROM tmp_films WHERE date_prod \u0026lt; '2004-05-07';\n\u003c/pre\u003e\u003cp\u003eThis example inserts into array columns:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003e-- Create an empty 3x3 gameboard for noughts-and-crosses\nINSERT INTO tictactoe (game, board[1:3][1:3])\n    VALUES (1, '{{\" \",\" \",\" \"},{\" \",\" \",\" \"},{\" \",\" \",\" \"}}');\n-- The subscripts in the above example aren't really needed\nINSERT INTO tictactoe (game, board)\n    VALUES (2, '{{X,\" \",\" \"},{\" \",O,\" \"},{\" \",X,\" \"}}');\n\u003c/pre\u003e\u003cp\u003eInsert a single row into table \u003ccode class=\"literal\"\u003edistributors\u003c/code\u003e, returning the sequence number generated by the \u003ccode class=\"literal\"\u003eDEFAULT\u003c/code\u003e clause:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eINSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')\n   RETURNING did;\n\u003c/pre\u003e\u003cp\u003eIncrement the sales count of the salesperson who manages the account for Acme Corporation, and record the whole updated row along with current time in a log table:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eWITH upd AS (\n  UPDATE employees SET sales_count = sales_count + 1 WHERE id =\n    (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation')\n    RETURNING *\n)\nINSERT INTO employees_log SELECT *, current_timestamp FROM upd;\n\u003c/pre\u003e\u003cp\u003eInsert or update new distributors as appropriate. Assumes a unique index has been defined that constrains values appearing in the \u003ccode class=\"literal\"\u003edid\u003c/code\u003e column. Note that the special \u003ccode class=\"varname\"\u003eexcluded\u003c/code\u003e table is used to reference values originally proposed for insertion:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eINSERT INTO distributors (did, dname)\n    VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')\n    ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;\n\u003c/pre\u003e\u003cp\u003eInsert or update new distributors as above, returning information about any existing values that were updated, together with the new data inserted. Note that the returned values for \u003ccode class=\"literal\"\u003eold_did\u003c/code\u003e and \u003ccode class=\"literal\"\u003eold_dname\u003c/code\u003e will be \u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e for non-conflicting rows:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eINSERT INTO distributors (did, dname)\n    VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')\n    ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname\n    RETURNING old.did AS old_did, old.dname AS old_dname,\n              new.did AS new_did, new.dname AS new_dname;\n\u003c/pre\u003e\u003cp\u003eInsert a distributor, or do nothing for rows proposed for insertion when an existing, excluded row (a row with a matching constrained column or columns after before row insert triggers fire) exists. Example assumes a unique index has been defined that constrains values appearing in the \u003ccode class=\"literal\"\u003edid\u003c/code\u003e column:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eINSERT INTO distributors (did, dname) VALUES (7, 'Redline GmbH')\n    ON CONFLICT (did) DO NOTHING;\n\u003c/pre\u003e\u003cp\u003eInsert or update new distributors as appropriate. Example assumes a unique index has been defined that constrains values appearing in the \u003ccode class=\"literal\"\u003edid\u003c/code\u003e column. \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e clause is used to limit the rows actually updated (any existing row not updated will still be locked, though):\u003c/p\u003e\u003cpre class=\"programlisting\"\u003e-- Don't update existing distributors based in a certain ZIP code\nINSERT INTO distributors AS d (did, dname) VALUES (8, 'Anvil Distribution')\n    ON CONFLICT (did) DO UPDATE\n    SET dname = EXCLUDED.dname || ' (formerly ' || d.dname || ')'\n    WHERE d.zipcode \u0026lt;\u0026gt; '21201';\n\n-- Name a constraint directly in the statement (uses associated\n-- index to arbitrate taking the DO NOTHING action)\nINSERT INTO distributors (did, dname) VALUES (9, 'Antwerp Design')\n    ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING;\n\u003c/pre\u003e\u003cp\u003eInsert new distributor if possible; otherwise \u003ccode class=\"literal\"\u003eDO NOTHING\u003c/code\u003e. Example assumes a unique index has been defined that constrains values appearing in the \u003ccode class=\"literal\"\u003edid\u003c/code\u003e column on a subset of rows where the \u003ccode class=\"literal\"\u003eis_active\u003c/code\u003e Boolean column evaluates to \u003ccode class=\"literal\"\u003etrue\u003c/code\u003e:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003e-- This statement could infer a partial unique index on \"did\"\n-- with a predicate of \"WHERE is_active\", but it could also\n-- just use a regular unique constraint on \"did\"\nINSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')\n    ON CONFLICT (did) WHERE is_active DO NOTHING;\n\u003c/pre\u003e","key":"examples","title":"Examples"},{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e conforms to the SQL standard, except that the \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e clause is a \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e extension, as is the ability to use \u003ccode class=\"literal\"\u003eWITH\u003c/code\u003e with \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e, and the ability to specify an alternative action with \u003ccode class=\"literal\"\u003eON CONFLICT\u003c/code\u003e. Also, the case in which a column name list is omitted, but not all the columns are filled from the \u003ccode class=\"literal\"\u003eVALUES\u003c/code\u003e clause or \u003cem class=\"replaceable\"\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e, is disallowed by the standard. If you prefer a more SQL standard conforming statement than \u003ccode class=\"literal\"\u003eON CONFLICT\u003c/code\u003e, see \u003ca href=\"/docs/18/sql-merge.html\" title=\"MERGE\"\u003e\u003cspan class=\"refentrytitle\"\u003eMERGE\u003c/span\u003e\u003c/a\u003e.\u003c/p\u003e\u003cp\u003eThe SQL standard specifies that \u003ccode class=\"literal\"\u003eOVERRIDING SYSTEM VALUE\u003c/code\u003e can only be specified if an identity column that is generated always exists. PostgreSQL allows the clause in any case and ignores it if it is not applicable.\u003c/p\u003e\u003cp\u003ePossible limitations of the \u003cem class=\"replaceable\"\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e clause are documented under \u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\"\u003e\u003cspan class=\"refentrytitle\"\u003eSELECT\u003c/span\u003e\u003c/a\u003e.\u003c/p\u003e","key":"compatibility","title":"Compatibility"}],"sections_same_as":"","slug":"18","synopsis_html":"[ WITH [ RECURSIVE ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003ewith_query\u003c/code\u003e\u003c/em\u003e [, ...] ]\nINSERT INTO \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e [ AS \u003cem class=\"replaceable\"\u003e\u003ccode\u003ealias\u003c/code\u003e\u003c/em\u003e ] [ ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e [, ...] ) ]\n    [ OVERRIDING { SYSTEM | USER } VALUE ]\n    { DEFAULT VALUES | VALUES ( { \u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e | DEFAULT } [, ...] ) [, ...] | \u003cem class=\"replaceable\"\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e }\n    [ ON CONFLICT [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003econflict_target\u003c/code\u003e\u003c/em\u003e ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003econflict_action\u003c/code\u003e\u003c/em\u003e ]\n    [ RETURNING [ WITH ( { OLD | NEW } AS \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_alias\u003c/code\u003e\u003c/em\u003e [, ...] ) ]\n                { * | \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_expression\u003c/code\u003e\u003c/em\u003e [ [ AS ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_name\u003c/code\u003e\u003c/em\u003e ] } [, ...] ]\n\n\u003cspan class=\"phrase\"\u003ewhere \u003cem class=\"replaceable\"\u003e\u003ccode\u003econflict_target\u003c/code\u003e\u003c/em\u003e can be one of:\u003c/span\u003e\n\n    ( { \u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_column_name\u003c/code\u003e\u003c/em\u003e | ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_expression\u003c/code\u003e\u003c/em\u003e ) } [ COLLATE \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecollation\u003c/code\u003e\u003c/em\u003e ] [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eopclass\u003c/code\u003e\u003c/em\u003e ] [, ...] ) [ WHERE \u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_predicate\u003c/code\u003e\u003c/em\u003e ]\n    ON CONSTRAINT \u003cem class=\"replaceable\"\u003e\u003ccode\u003econstraint_name\u003c/code\u003e\u003c/em\u003e\n\n\u003cspan class=\"phrase\"\u003eand \u003cem class=\"replaceable\"\u003e\u003ccode\u003econflict_action\u003c/code\u003e\u003c/em\u003e is one of:\u003c/span\u003e\n\n    DO NOTHING\n    DO UPDATE SET { \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e = { \u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e | DEFAULT } |\n                    ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e [, ...] ) = [ ROW ] ( { \u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e | DEFAULT } [, ...] ) |\n                    ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e [, ...] ) = ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003esub-SELECT\u003c/code\u003e\u003c/em\u003e )\n                  } [, ...]\n              [ WHERE \u003cem class=\"replaceable\"\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e ]","synopsis_text":"[ WITH [ RECURSIVE ] with_query [, ...] ]\nINSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]\n[ OVERRIDING { SYSTEM | USER } VALUE ]\n{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }\n[ ON CONFLICT [ conflict_target ] conflict_action ]\n[ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ]\n{ * | output_expression [ [ AS ] output_name ] } [, ...] ]\n\nwhere conflict_target can be one of:\n\n( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]\nON CONSTRAINT constraint_name\nand conflict_action is one of:\n\nDO NOTHING\nDO UPDATE SET { column_name = { expression | DEFAULT } |\n( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |\n( column_name [, ...] ) = ( sub-SELECT )\n} [, ...]\n[ WHERE condition ]"},"ManualEvidence":{},"MeasuredEvidence":{}},"Text":{"Collection":"sql","Key":"insert","SourceDatabase":"pgweb","Version":"18","Locale":"zh-Hans","Title":"INSERT","Summary":"在表中插入新行","BodyHTML":"\u003cpre\u003e[ WITH [ RECURSIVE ] with_query [, ...] ]\nINSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]\n[ OVERRIDING { SYSTEM | USER } VALUE ]\n{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }\n[ ON CONFLICT [ conflict_target ] conflict_action ]\n[ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ]\n{ * | output_expression [ [ AS ] output_name ] } [, ...] ]\n\n其中 conflict_target 可以是以下之一：\n\n( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]\nON CONSTRAINT constraint_name\n其中 conflict_action 是以下之一：\n\nDO NOTHING\nDO UPDATE SET { column_name = { expression | DEFAULT } |\n( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |\n( column_name [, ...] ) = ( sub-SELECT )\n} [, ...]\n[ WHERE condition ]\u003c/pre\u003e\u003csection\u003e\u003ch2\u003e描述\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eINSERT\u003c/code\u003e将新行插入表中。可以插入由值表达式指定的一行或多行，也可以插入由查询产生的零行或多行。\u003c/p\u003e\u003cp\u003e目标列名可以按任意顺序列出。如果根本未给出列名列表，则默认使用按声明顺序排列的全部列；或者如果\u003ccode\u003eVALUES\u003c/code\u003e子句或 \u003cem\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e只提供了\u003cem\u003e\u003ccode\u003eN\u003c/code\u003e\u003c/em\u003e列，则默认使用按声明顺序排列的前\u003cem\u003e\u003ccode\u003eN\u003c/code\u003e\u003c/em\u003e个列名。\u003ccode\u003eVALUES\u003c/code\u003e子句或\u003cem\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e 提供的值，会按从左到右的顺序与显式或隐式列列表对应起来。\u003c/p\u003e\u003cp\u003e任何未出现在显式或隐式列列表中的列都会填入默认值；如果没有声明默认值，则填入空值。\u003c/p\u003e\u003cp\u003e如果任何列的表达式数据类型不正确，将尝试自动进行类型转换。\u003c/p\u003e\u003cp\u003e对没有唯一索引的表执行\u003ccode\u003eINSERT\u003c/code\u003e不会被并发活动阻塞。若并发会话执行的操作会锁定或修改与待插入唯一索引值匹配的行，则带有唯一索引的表可能发生阻塞；详见\u003ca href=\"/docs/18/index-unique-checks.html\" rel=\"nofollow\"\u003e第 63.5 节\u003c/a\u003e。\u003ccode\u003eON CONFLICT\u003c/code\u003e可用于指定一种替代动作，而不是报出违反唯一约束或排他约束的错误。（见下文\u003ca href=\"/docs/18/sql-insert.html#SQL-ON-CONFLICT\" title=\"ON CONFLICT 子句\" rel=\"nofollow\"\u003eON CONFLICT Clause\u003c/a\u003e。）\u003c/p\u003e\u003cp\u003e可选的\u003ccode\u003eRETURNING\u003c/code\u003e子句使\u003ccode\u003eINSERT\u003c/code\u003e 基于每个实际插入的行（若使用了\u003ccode\u003eON CONFLICT DO UPDATE\u003c/code\u003e 子句，则也可能是更新后的行）计算并返回一个或多个值。这主要用于获取由默认值提供的值，例如 serial 序列号。不过，也允许使用任何引用该表列的表达式。\u003ccode\u003eRETURNING\u003c/code\u003e列表的语法与 \u003ccode\u003eSELECT\u003c/code\u003e的输出列表相同。只有成功插入或更新的行才会被返回。例如，如果某一行被锁定，但由于不满足 \u003ccode\u003eON CONFLICT DO UPDATE ... WHERE\u003c/code\u003e子句中的 \u003cem\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e而未被更新，则该行不会被返回。\u003c/p\u003e\u003cp\u003e要向表中插入行，必须具有该表上的\u003ccode\u003eINSERT\u003c/code\u003e权限。如果存在\u003ccode\u003eON CONFLICT DO UPDATE\u003c/code\u003e子句，还要求具有该表上的\u003ccode\u003eUPDATE\u003c/code\u003e权限。\u003c/p\u003e\u003cp\u003e如果指定了列列表，你只需要对所列出的列具有\u003ccode\u003eINSERT\u003c/code\u003e权限。类似地，在指定\u003ccode\u003eON CONFLICT DO UPDATE\u003c/code\u003e时，你只需要对列出要更新的列具有\u003ccode\u003eUPDATE\u003c/code\u003e权限。不过，所有形式的\u003ccode\u003eON CONFLICT\u003c/code\u003e还要求对任何值会被读取的列具有\u003ccode\u003eSELECT\u003c/code\u003e权限。这包括 \u003cem\u003e\u003ccode\u003econflict_target\u003c/code\u003e\u003c/em\u003e中提到的任何列（包括仲裁约束引用的列），以及\u003ccode\u003eON CONFLICT DO UPDATE\u003c/code\u003e的 \u003cem\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e或\u003ccode\u003eWHERE\u003c/code\u003e子句 \u003cem\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e中提到的任何列。\u003c/p\u003e\u003cp\u003e使用\u003ccode\u003eRETURNING\u003c/code\u003e子句要求对 \u003ccode\u003eRETURNING\u003c/code\u003e中提到的所有列具有 \u003ccode\u003eSELECT\u003c/code\u003e权限。如果使用\u003cem\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e子句将查询结果中的行插入表中，那么当然还需要对查询中用到的任何表或列具有\u003ccode\u003eSELECT\u003c/code\u003e 权限。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e参数\u003c/h2\u003e\u003cdiv\u003e\u003ch3\u003e插入\u003c/h3\u003e\u003cp\u003e本节介绍仅在插入新行时可用的参数。\u003cspan\u003e\u003cem\u003e专门用于\u003c/em\u003e\u003c/span\u003e \u003ccode\u003eON CONFLICT\u003c/code\u003e子句的参数将单独说明。\u003c/p\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ewith_query\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003ccode\u003eWITH\u003c/code\u003e子句允许指定一个或多个子查询，这些子查询可以在\u003ccode\u003eINSERT\u003c/code\u003e查询中按名称引用。详见\u003ca href=\"/docs/18/queries-with.html\" rel=\"nofollow\"\u003e第 7.8 节\u003c/a\u003e和\u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\" rel=\"nofollow\"\u003e\u003cspan\u003eSELECT\u003c/span\u003e\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e\u003cem\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e（\u003ccode\u003eSELECT\u003c/code\u003e语句）本身也可以包含 \u003ccode\u003eWITH\u003c/code\u003e子句。在这种情况下，\u003cem\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e中可以引用两组 \u003cem\u003e\u003ccode\u003ewith_query\u003c/code\u003e\u003c/em\u003e，但由于第二组嵌套得更近，它具有更高的优先级。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e一个现有表的名称（可以是模式限定的）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ealias\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003cem\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e的替代名称。提供别名后，它会完全隐藏表的实际名称。当 \u003ccode\u003eON CONFLICT DO UPDATE\u003c/code\u003e的目标是一个名为 \u003ccode\u003eexcluded\u003c/code\u003e的表时，这一点特别有用，因为否则该名称会被当作表示拟插入行的那个特殊表名。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e名为\u003cem\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e的表中某一列的名称。如有需要，列名可以附带子字段名或数组下标。（只向复合列的部分字段插入值时，其余字段将为空值。）在 \u003ccode\u003eON CONFLICT DO UPDATE\u003c/code\u003e中引用列时，不要在目标列的指定中包含表名。例如，\u003ccode\u003eINSERT INTO table_name ... ON CONFLICT DO UPDATE SET table_name.col = 1\u003c/code\u003e是无效的（这与\u003ccode\u003eUPDATE\u003c/code\u003e的一般行为一致）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eOVERRIDING SYSTEM VALUE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e如果指定了此子句，那么为标识列提供的任何值都将覆盖默认的序列生成值。\u003c/p\u003e\u003cp\u003e对于定义为\u003ccode\u003eGENERATED ALWAYS\u003c/code\u003e的标识列，如果插入显式值（\u003ccode\u003eDEFAULT\u003c/code\u003e除外）而未指定 \u003ccode\u003eOVERRIDING SYSTEM VALUE\u003c/code\u003e或 \u003ccode\u003eOVERRIDING USER VALUE\u003c/code\u003e，则会报错。（对于定义为\u003ccode\u003eGENERATED BY DEFAULT\u003c/code\u003e的标识列，\u003ccode\u003eOVERRIDING SYSTEM VALUE\u003c/code\u003e本来就是默认行为，因而显式指定它不会产生任何效果，但 \u003cspan\u003ePostgreSQL\u003c/span\u003e仍将其作为扩展允许使用。）\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eOVERRIDING USER VALUE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e如果指定了此子句，则会忽略为标识列提供的任何值，并应用默认的序列生成值。\u003c/p\u003e\u003cp\u003e例如，在表之间复制值时，这个子句很有用。写成 \u003ccode\u003eINSERT INTO tbl2 OVERRIDING USER VALUE SELECT * FROM tbl1\u003c/code\u003e将从\u003ccode\u003etbl1\u003c/code\u003e复制所有在 \u003ccode\u003etbl2\u003c/code\u003e中不是标识列的列，而 \u003ccode\u003etbl2\u003c/code\u003e中标识列的值将由与 \u003ccode\u003etbl2\u003c/code\u003e关联的序列生成。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eDEFAULT VALUES\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e所有列都会填入各自的默认值，就像为每一列都显式指定了 \u003ccode\u003eDEFAULT\u003c/code\u003e一样。（这种形式不允许出现 \u003ccode\u003eOVERRIDING\u003c/code\u003e子句。）\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e赋给相应列的表达式或值。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eDEFAULT\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e相应列将填入其默认值。标识列将填入由关联序列生成的新值。对于生成列，允许指定该关键字，但这只是表明采用根据其生成表达式计算该列这一正常行为。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e提供要插入行的查询（\u003ccode\u003eSELECT\u003c/code\u003e语句）。其语法说明请参见\u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\" rel=\"nofollow\"\u003e\u003cspan\u003eSELECT\u003c/span\u003e\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eoutput_alias\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e在\u003ccode\u003eRETURNING\u003c/code\u003e列表中为\u003ccode\u003eOLD\u003c/code\u003e或 \u003ccode\u003eNEW\u003c/code\u003e行指定的可选替代名称。\u003c/p\u003e\u003cp\u003e默认情况下，可通过 \u003ccode\u003eOLD.\u003cem\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e 或\u003ccode\u003eOLD.*\u003c/code\u003e返回目标表旧值，通过 \u003ccode\u003eNEW.\u003cem\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e 或\u003ccode\u003eNEW.*\u003c/code\u003e返回新值。提供别名后，这些名称会被隐藏，必须使用别名来引用旧行或新行。例如 \u003ccode\u003eRETURNING WITH (OLD AS o, NEW AS n) o.*, n.*\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eoutput_expression\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e在每一行插入或更新后由\u003ccode\u003eINSERT\u003c/code\u003e命令计算并返回的表达式。该表达式可以使用由\u003cem\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e指定的表中的任何列。写成\u003ccode\u003e*\u003c/code\u003e可返回插入或更新后行的全部列。\u003c/p\u003e\u003cp\u003e列名或\u003ccode\u003e*\u003c/code\u003e可以用\u003ccode\u003eOLD\u003c/code\u003e或 \u003ccode\u003eNEW\u003c/code\u003e进行限定，也可以用 \u003ccode\u003eOLD\u003c/code\u003e或\u003ccode\u003eNEW\u003c/code\u003e对应的 \u003cem\u003e\u003ccode\u003eoutput_alias\u003c/code\u003e\u003c/em\u003e进行限定，以返回旧值或新值。未限定的列名或\u003ccode\u003e*\u003c/code\u003e，以及使用目标表名或别名限定的列名或\u003ccode\u003e*\u003c/code\u003e，都会返回新值。\u003c/p\u003e\u003cp\u003e对于普通\u003ccode\u003eINSERT\u003c/code\u003e，所有旧值都为 \u003ccode\u003eNULL\u003c/code\u003e。但对于带 \u003ccode\u003eON CONFLICT DO UPDATE\u003c/code\u003e子句的 \u003ccode\u003eINSERT\u003c/code\u003e，旧值可能为非\u003ccode\u003eNULL\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eoutput_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e用于返回列的名称。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/div\u003e\u003cdiv\u003e\u003ch3\u003e\u003ccode\u003eON CONFLICT\u003c/code\u003e 子句\u003c/h3\u003e\u003cp\u003e可选的\u003ccode\u003eON CONFLICT\u003c/code\u003e子句指定一种替代动作，用来替代抛出唯一约束或排他约束违背错误。对于每一条拟插入的行，要么插入继续进行；要么如果违反了由\u003cem\u003e\u003ccode\u003econflict_target\u003c/code\u003e\u003c/em\u003e 指定的某个\u003cspan\u003e\u003cem\u003e仲裁\u003c/em\u003e\u003c/span\u003e约束或索引，就执行替代的 \u003cem\u003e\u003ccode\u003econflict_action\u003c/code\u003e\u003c/em\u003e。\u003ccode\u003eON CONFLICT DO NOTHING\u003c/code\u003e的替代动作只是跳过该行的插入。\u003ccode\u003eON CONFLICT DO UPDATE\u003c/code\u003e的替代动作则是更新与拟插入行冲突的现有行。\u003c/p\u003e\u003cp\u003e\u003cem\u003e\u003ccode\u003econflict_target\u003c/code\u003e\u003c/em\u003e可以进行\u003cspan\u003e\u003cem\u003e唯一索引推断\u003c/em\u003e\u003c/span\u003e。执行推断时，它由一个或多个 \u003cem\u003e\u003ccode\u003eindex_column_name\u003c/code\u003e\u003c/em\u003e列和/或\u003cem\u003e\u003ccode\u003eindex_expression\u003c/code\u003e\u003c/em\u003e 表达式，以及可选的\u003cem\u003e\u003ccode\u003eindex_predicate\u003c/code\u003e\u003c/em\u003e 组成。所有在不考虑顺序的情况下恰好包含 \u003cem\u003e\u003ccode\u003econflict_target\u003c/code\u003e\u003c/em\u003e指定列/表达式的 \u003cem\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e唯一索引，都会被推断（选中）为仲裁索引。如果指定了 \u003cem\u003e\u003ccode\u003eindex_predicate\u003c/code\u003e\u003c/em\u003e，则作为推断的进一步要求，候选仲裁索引还必须满足该谓词。注意，这意味着如果存在某个满足其他所有条件的非部分唯一索引（即没有谓词的唯一索引），那么该索引也会被推断出来（从而被\u003ccode\u003eON CONFLICT\u003c/code\u003e 使用）。如果推断尝试失败，则会报错。\u003c/p\u003e\u003cp\u003e\u003ccode\u003eON CONFLICT DO UPDATE\u003c/code\u003e保证得到原子的 \u003ccode\u003eINSERT\u003c/code\u003e或\u003ccode\u003eUPDATE\u003c/code\u003e结果；只要没有其他独立错误，即使在高并发下，也能保证结果是这两者之一。这也称为\u003cem\u003eUPSERT\u003c/em\u003e — \u003cspan\u003e“\u003cspan\u003eUPDATE or INSERT\u003c/span\u003e”\u003c/span\u003e。\u003c/p\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003econflict_target\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e通过选择\u003cem\u003e仲裁索引\u003c/em\u003e来指定 \u003ccode\u003eON CONFLICT\u003c/code\u003e对哪些冲突采取替代动作。它要么执行\u003cspan\u003e\u003cem\u003e唯一索引推断\u003c/em\u003e\u003c/span\u003e，要么显式命名一个约束。对于\u003ccode\u003eON CONFLICT DO NOTHING\u003c/code\u003e，是否指定 \u003cem\u003e\u003ccode\u003econflict_target\u003c/code\u003e\u003c/em\u003e是可选的；省略时，将处理与所有可用约束（以及唯一索引）的冲突。对于 \u003ccode\u003eON CONFLICT DO UPDATE\u003c/code\u003e，则\u003cspan\u003e\u003cem\u003e必须\u003c/em\u003e\u003c/span\u003e提供\u003cem\u003e\u003ccode\u003econflict_target\u003c/code\u003e\u003c/em\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003econflict_action\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003cem\u003e\u003ccode\u003econflict_action\u003c/code\u003e\u003c/em\u003e指定一个替代的 \u003ccode\u003eON CONFLICT\u003c/code\u003e动作。它可以是 \u003ccode\u003eDO NOTHING\u003c/code\u003e，也可以是 \u003ccode\u003eDO UPDATE\u003c/code\u003e子句，用以精确指定发生冲突时要执行的\u003ccode\u003eUPDATE\u003c/code\u003e动作细节。在 \u003ccode\u003eON CONFLICT DO UPDATE\u003c/code\u003e中，\u003ccode\u003eSET\u003c/code\u003e和\u003ccode\u003eWHERE\u003c/code\u003e子句既可以通过表名（或别名）访问现有行，也可以通过特殊表 \u003ccode\u003eexcluded\u003c/code\u003e访问拟插入的行。对于任何被读取的\u003ccode\u003eexcluded\u003c/code\u003e列，必须对目标表中的对应列具有\u003ccode\u003eSELECT\u003c/code\u003e权限。\u003c/p\u003e\u003cp\u003e注意，所有行级\u003ccode\u003eBEFORE INSERT\u003c/code\u003e触发器的效果都会反映在\u003ccode\u003eexcluded\u003c/code\u003e值中，因为这些效果可能促成该行被排除在插入之外。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eindex_column_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003cem\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e中某一列的名称。用于推断仲裁索引。遵循\u003ccode\u003eCREATE INDEX\u003c/code\u003e 的格式。要求对\u003cem\u003e\u003ccode\u003eindex_column_name\u003c/code\u003e\u003c/em\u003e 具有\u003ccode\u003eSELECT\u003c/code\u003e权限。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eindex_expression\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e与\u003cem\u003e\u003ccode\u003eindex_column_name\u003c/code\u003e\u003c/em\u003e 类似，但用于推断出现在索引定义中的、基于\u003cem\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e列的表达式（而非简单列）。遵循\u003ccode\u003eCREATE INDEX\u003c/code\u003e的格式。要求对出现在\u003cem\u003e\u003ccode\u003eindex_expression\u003c/code\u003e\u003c/em\u003e 中的任何列具有\u003ccode\u003eSELECT\u003c/code\u003e权限。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ecollation\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定时，要求相应的\u003cem\u003e\u003ccode\u003eindex_column_name\u003c/code\u003e\u003c/em\u003e或 \u003cem\u003e\u003ccode\u003eindex_expression\u003c/code\u003e\u003c/em\u003e 必须使用特定的排序规则，才能在推断时匹配。通常会省略，因为排序规则通常不会影响是否发生约束违背。遵循 \u003ccode\u003eCREATE INDEX\u003c/code\u003e格式。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eopclass\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定时，要求相应的\u003cem\u003e\u003ccode\u003eindex_column_name\u003c/code\u003e\u003c/em\u003e或 \u003cem\u003e\u003ccode\u003eindex_expression\u003c/code\u003e\u003c/em\u003e 必须使用特定的操作符类，才能在推断时匹配。通常会省略，因为一种类型的各个操作符类在\u003cspan\u003e\u003cem\u003e相等性\u003c/em\u003e\u003c/span\u003e语义上往往是等价的，或者只需相信已定义的唯一索引具有所需的相等性定义即可。遵循\u003ccode\u003eCREATE INDEX\u003c/code\u003e格式。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eindex_predicate\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e用于允许推断部分唯一索引。任何满足该谓词的索引（实际上不一定是部分索引）都可以被推断。遵循\u003ccode\u003eCREATE INDEX\u003c/code\u003e 格式。要求对出现在\u003cem\u003e\u003ccode\u003eindex_predicate\u003c/code\u003e\u003c/em\u003e 中的任何列具有\u003ccode\u003eSELECT\u003c/code\u003e权限。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003econstraint_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e显式按名称指定一个作为仲裁的\u003cspan\u003e\u003cem\u003e约束\u003c/em\u003e\u003c/span\u003e，而不是通过推断约束或索引。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e返回\u003ccode\u003eboolean\u003c/code\u003e值的表达式。只有使该表达式返回 \u003ccode\u003etrue\u003c/code\u003e的行才会被更新，不过一旦采取 \u003ccode\u003eON CONFLICT DO UPDATE\u003c/code\u003e动作，所有行都会被锁定。请注意，只有在某个冲突已被识别为更新候选后，才会最后计算\u003cem\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003cp\u003e请注意，排他约束不支持在\u003ccode\u003eON CONFLICT DO UPDATE\u003c/code\u003e 中充当仲裁对象。在所有情况下，只有\u003ccode\u003eNOT DEFERRABLE\u003c/code\u003e 约束和唯一索引可作为仲裁对象。\u003c/p\u003e\u003cp\u003e带有\u003ccode\u003eON CONFLICT DO UPDATE\u003c/code\u003e子句的 \u003ccode\u003eINSERT\u003c/code\u003e是一种\u003cspan\u003e“\u003cspan\u003e确定性\u003c/span\u003e”\u003c/span\u003e语句。这意味着不允许该命令对任何单个现有行产生多于一次的影响；一旦出现这种情况，就会报出基数违背错误。拟插入的行在受仲裁索引或约束限制的属性上不应彼此重复。\u003c/p\u003e\u003cp\u003e请注意，目前尚不支持在针对分区表的\u003ccode\u003eINSERT\u003c/code\u003e的 \u003ccode\u003eON CONFLICT DO UPDATE\u003c/code\u003e子句中更新冲突行的分区键，从而需要将该行移动到一个新分区。\u003c/p\u003e\u003cdiv\u003e\u003ch3\u003e提示\u003c/h3\u003e\u003cp\u003e通常更推荐使用唯一索引推断，而不是通过 \u003ccode\u003eON CONFLICT ON CONSTRAINT\u003c/code\u003e \u003cem\u003e\u003ccode\u003econstraint_name\u003c/code\u003e\u003c/em\u003e直接命名约束。当底层索引以重叠方式被另一个大致等价的索引替换时，推断仍能继续正确工作，例如在删除待替换索引之前先使用 \u003ccode\u003eCREATE UNIQUE INDEX ... CONCURRENTLY\u003c/code\u003e。\u003c/p\u003e\u003c/div\u003e\u003cdiv\u003e\u003ch3\u003e警告\u003c/h3\u003e\u003cp\u003e当某个唯一索引上正在执行 \u003ccode\u003eCREATE INDEX CONCURRENTLY\u003c/code\u003e或 \u003ccode\u003eREINDEX CONCURRENTLY\u003c/code\u003e时，同一张表上的 \u003ccode\u003eINSERT ... ON CONFLICT\u003c/code\u003e语句可能会意外地因唯一性违反而失败。\u003c/p\u003e\u003c/div\u003e\u003c/div\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e输出\u003c/h2\u003e\u003cp\u003e成功完成时，\u003ccode\u003eINSERT\u003c/code\u003e命令会返回如下形式的命令标签：\u003c/p\u003e\u003cpre\u003eINSERT \u003cem\u003e\u003ccode\u003eoid\u003c/code\u003e\u003c/em\u003e \u003cem\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\n\u003c/pre\u003e\u003cp\u003e\u003cem\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e是插入或更新的行数。\u003cem\u003e\u003ccode\u003eoid\u003c/code\u003e\u003c/em\u003e始终为 0（过去，如果 \u003cem\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e恰好为 1 且目标表声明为 \u003ccode\u003eWITH OIDS\u003c/code\u003e，则它是分配给插入行的 \u003cacronym\u003eOID\u003c/acronym\u003e，否则为 0；但现在已经不再支持创建 \u003ccode\u003eWITH OIDS\u003c/code\u003e表）。\u003c/p\u003e\u003cp\u003e如果\u003ccode\u003eINSERT\u003c/code\u003e命令包含\u003ccode\u003eRETURNING\u003c/code\u003e 子句，则结果将类似于一个\u003ccode\u003eSELECT\u003c/code\u003e语句，其中包含 \u003ccode\u003eRETURNING\u003c/code\u003e列表定义的列和值，它们是基于该命令插入或更新的行计算出来的。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e注解\u003c/h2\u003e\u003cp\u003e如果指定的表是分区表，则每一行都会被路由到适当的分区并插入其中。如果指定的表本身就是一个分区，则只要输入行中有任意一行违反该分区约束，就会报错。\u003c/p\u003e\u003cp\u003e也可以考虑使用\u003ccode\u003eMERGE\u003c/code\u003e，因为它允许在单条语句中混合\u003ccode\u003eINSERT\u003c/code\u003e、\u003ccode\u003eUPDATE\u003c/code\u003e和 \u003ccode\u003eDELETE\u003c/code\u003e。参见\u003ca href=\"/docs/18/sql-merge.html\" title=\"MERGE\" rel=\"nofollow\"\u003e\u003cspan\u003eMERGE\u003c/span\u003e\u003c/a\u003e。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e示例\u003c/h2\u003e\u003cp\u003e向表\u003ccode\u003efilms\u003c/code\u003e插入一行：\u003c/p\u003e\u003cpre\u003eINSERT INTO films VALUES\n    (\u0026#39;UA502\u0026#39;, \u0026#39;Bananas\u0026#39;, 105, \u0026#39;1971-07-13\u0026#39;, \u0026#39;Comedy\u0026#39;, \u0026#39;82 minutes\u0026#39;);\n\u003c/pre\u003e\u003cp\u003e在这个示例中，省略了\u003ccode\u003elen\u003c/code\u003e列，因此它将取默认值：\u003c/p\u003e\u003cpre\u003eINSERT INTO films (code, title, did, date_prod, kind)\n    VALUES (\u0026#39;T_601\u0026#39;, \u0026#39;Yojimbo\u0026#39;, 106, \u0026#39;1961-06-16\u0026#39;, \u0026#39;Drama\u0026#39;);\n\u003c/pre\u003e\u003cp\u003e这个示例对日期列使用\u003ccode\u003eDEFAULT\u003c/code\u003e子句，而不是显式指定值：\u003c/p\u003e\u003cpre\u003eINSERT INTO films VALUES\n    (\u0026#39;UA502\u0026#39;, \u0026#39;Bananas\u0026#39;, 105, DEFAULT, \u0026#39;Comedy\u0026#39;, \u0026#39;82 minutes\u0026#39;);\nINSERT INTO films (code, title, did, date_prod, kind)\n    VALUES (\u0026#39;T_601\u0026#39;, \u0026#39;Yojimbo\u0026#39;, 106, DEFAULT, \u0026#39;Drama\u0026#39;);\n\u003c/pre\u003e\u003cp\u003e要插入一行，其所有列都由默认值构成：\u003c/p\u003e\u003cpre\u003eINSERT INTO films DEFAULT VALUES;\n\u003c/pre\u003e\u003cp\u003e使用多行\u003ccode\u003eVALUES\u003c/code\u003e语法插入多行：\u003c/p\u003e\u003cpre\u003eINSERT INTO films (code, title, did, date_prod, kind) VALUES\n    (\u0026#39;B6717\u0026#39;, \u0026#39;Tampopo\u0026#39;, 110, \u0026#39;1985-02-10\u0026#39;, \u0026#39;Comedy\u0026#39;),\n    (\u0026#39;HG120\u0026#39;, \u0026#39;The Dinner Game\u0026#39;, 140, DEFAULT, \u0026#39;Comedy\u0026#39;);\n\u003c/pre\u003e\u003cp\u003e这个示例从与\u003ccode\u003efilms\u003c/code\u003e列布局相同的 \u003ccode\u003etmp_films\u003c/code\u003e表中选出一些行并插入到 \u003ccode\u003efilms\u003c/code\u003e表：\u003c/p\u003e\u003cpre\u003eINSERT INTO films SELECT * FROM tmp_films WHERE date_prod \u0026lt; \u0026#39;2004-05-07\u0026#39;;\n\u003c/pre\u003e\u003cp\u003e这个示例向数组列插入值：\u003c/p\u003e\u003cpre\u003e-- 为井字棋创建一个空的 3x3 棋盘\nINSERT INTO tictactoe (game, board[1:3][1:3])\n    VALUES (1, \u0026#39;{{\u0026#34; \u0026#34;,\u0026#34; \u0026#34;,\u0026#34; \u0026#34;},{\u0026#34; \u0026#34;,\u0026#34; \u0026#34;,\u0026#34; \u0026#34;},{\u0026#34; \u0026#34;,\u0026#34; \u0026#34;,\u0026#34; \u0026#34;}}\u0026#39;);\n-- 上例中的下标实际上并非必需\nINSERT INTO tictactoe (game, board)\n    VALUES (2, \u0026#39;{{X,\u0026#34; \u0026#34;,\u0026#34; \u0026#34;},{\u0026#34; \u0026#34;,O,\u0026#34; \u0026#34;},{\u0026#34; \u0026#34;,X,\u0026#34; \u0026#34;}}\u0026#39;);\n\u003c/pre\u003e\u003cp\u003e向表\u003ccode\u003edistributors\u003c/code\u003e插入一行，并返回由 \u003ccode\u003eDEFAULT\u003c/code\u003e子句生成的序号：\u003c/p\u003e\u003cpre\u003eINSERT INTO distributors (did, dname) VALUES (DEFAULT, \u0026#39;XYZ Widgets\u0026#39;)\n   RETURNING did;\n\u003c/pre\u003e\u003cp\u003e为负责 Acme Corporation 账户的销售人员增加销量计数，并把整个更新后的行连同当前时间记录到日志表中：\u003c/p\u003e\u003cpre\u003eWITH upd AS (\n  UPDATE employees SET sales_count = sales_count + 1 WHERE id =\n    (SELECT sales_person FROM accounts WHERE name = \u0026#39;Acme Corporation\u0026#39;)\n    RETURNING *\n)\nINSERT INTO employees_log SELECT *, current_timestamp FROM upd;\n\u003c/pre\u003e\u003cp\u003e视情况插入或更新新的分销商。假设已经定义了一个唯一索引，用于约束出现在\u003ccode\u003edid\u003c/code\u003e列中的值。请注意，特殊的 \u003ccode\u003eexcluded\u003c/code\u003e表用于引用最初拟插入的值：\u003c/p\u003e\u003cpre\u003eINSERT INTO distributors (did, dname)\n    VALUES (5, \u0026#39;Gizmo Transglobal\u0026#39;), (6, \u0026#39;Associated Computing, Inc\u0026#39;)\n    ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;\n\u003c/pre\u003e\u003cp\u003e按照上例插入或更新新的分销商，并返回任何被更新的既有值以及插入的新数据。请注意，对于没有冲突的行，返回的\u003ccode\u003eold_did\u003c/code\u003e 和\u003ccode\u003eold_dname\u003c/code\u003e值将为\u003ccode\u003eNULL\u003c/code\u003e：\u003c/p\u003e\u003cpre\u003eINSERT INTO distributors (did, dname)\n    VALUES (5, \u0026#39;Gizmo Transglobal\u0026#39;), (6, \u0026#39;Associated Computing, Inc\u0026#39;)\n    ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname\n    RETURNING old.did AS old_did, old.dname AS old_dname,\n              new.did AS new_did, new.dname AS new_dname;\n\u003c/pre\u003e\u003cp\u003e插入一个分销商；如果已存在一条会导致待插入行被排除的现有行（即在行级\u003ccode\u003eBEFORE INSERT\u003c/code\u003e触发器触发后，其受约束列与待插入行匹配的行），则对该待插入行不做任何操作。示例假设已经定义了一个唯一索引，用于约束出现在\u003ccode\u003edid\u003c/code\u003e列中的值：\u003c/p\u003e\u003cpre\u003eINSERT INTO distributors (did, dname) VALUES (7, \u0026#39;Redline GmbH\u0026#39;)\n    ON CONFLICT (did) DO NOTHING;\n\u003c/pre\u003e\u003cp\u003e视情况插入或更新新的分销商。示例假设已经定义了一个唯一索引，用于约束出现在\u003ccode\u003edid\u003c/code\u003e列中的值。这里使用 \u003ccode\u003eWHERE\u003c/code\u003e子句来限制实际会被更新的行（不过，任何未更新的现有行仍会被锁定）：\u003c/p\u003e\u003cpre\u003e-- 不更新位于某个邮政编码区域内的现有分销商\nINSERT INTO distributors AS d (did, dname) VALUES (8, \u0026#39;Anvil Distribution\u0026#39;)\n    ON CONFLICT (did) DO UPDATE\n    SET dname = EXCLUDED.dname || \u0026#39; (formerly \u0026#39; || d.dname || \u0026#39;)\u0026#39;\n    WHERE d.zipcode \u0026lt;\u0026gt; \u0026#39;21201\u0026#39;;\n\n-- 在语句中直接指定约束名称（使用关联的\n-- 索引来仲裁是否采取 DO NOTHING 动作）\nINSERT INTO distributors (did, dname) VALUES (9, \u0026#39;Antwerp Design\u0026#39;)\n    ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING;\n\u003c/pre\u003e\u003cp\u003e如果可能就插入新的分销商；否则执行 \u003ccode\u003eDO NOTHING\u003c/code\u003e。示例假设已经定义了一个唯一索引，用于约束这样一个行子集上的\u003ccode\u003edid\u003c/code\u003e列值：其中 \u003ccode\u003eis_active\u003c/code\u003e布尔列计算结果为\u003ccode\u003etrue\u003c/code\u003e：\u003c/p\u003e\u003cpre\u003e-- 这条语句可以推断出 \u0026#34;did\u0026#34; 上以 \u0026#34;WHERE is_active\u0026#34;\n-- 为谓词的部分唯一索引，但也可以直接使用\n-- \u0026#34;did\u0026#34; 上的普通唯一约束\nINSERT INTO distributors (did, dname) VALUES (10, \u0026#39;Conrad International\u0026#39;)\n    ON CONFLICT (did) WHERE is_active DO NOTHING;\n\u003c/pre\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e兼容性\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eINSERT\u003c/code\u003e符合 SQL 标准，但 \u003ccode\u003eRETURNING\u003c/code\u003e子句是 \u003cspan\u003ePostgreSQL\u003c/span\u003e扩展，在 \u003ccode\u003eINSERT\u003c/code\u003e中使用\u003ccode\u003eWITH\u003c/code\u003e的能力以及使用\u003ccode\u003eON CONFLICT\u003c/code\u003e指定替代动作的能力也都是扩展。此外，标准不允许省略列名列表却又不是所有列都由\u003ccode\u003eVALUES\u003c/code\u003e 子句或\u003cem\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e填充的情况。如果你希望使用比\u003ccode\u003eON CONFLICT\u003c/code\u003e更符合 SQL 标准的语句，请参见\u003ca href=\"/docs/18/sql-merge.html\" title=\"MERGE\" rel=\"nofollow\"\u003e\u003cspan\u003eMERGE\u003c/span\u003e\u003c/a\u003e。\u003c/p\u003e\u003cp\u003eSQL 标准规定，只有当存在一个定义为总是生成值的标识列时，才能指定 \u003ccode\u003eOVERRIDING SYSTEM VALUE\u003c/code\u003e。而 \u003cspan\u003ePostgreSQL\u003c/span\u003e在任何情况下都允许该子句，并在它不适用时忽略它。\u003c/p\u003e\u003cp\u003e\u003cem\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e子句可能存在的限制见\u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\" rel=\"nofollow\"\u003e\u003cspan\u003eSELECT\u003c/span\u003e\u003c/a\u003e。\u003c/p\u003e\u003c/section\u003e","SourceRevision":"ca936764","ContentHash":"bd85353b313d95edac346439bcd099f0ea52c75de0f71d0b9da452d5f8aeb6e0","Payload":{"purpose_zh":"在表中插入新行","sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e将新行插入表中。可以插入由值表达式指定的一行或多行，也可以插入由查询产生的零行或多行。\u003c/p\u003e\u003cp\u003e目标列名可以按任意顺序列出。如果根本未给出列名列表，则默认使用按声明顺序排列的全部列；或者如果\u003ccode class=\"literal\"\u003eVALUES\u003c/code\u003e子句或 \u003cem class=\"replaceable\"\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e只提供了\u003cem class=\"replaceable\"\u003e\u003ccode\u003eN\u003c/code\u003e\u003c/em\u003e列，则默认使用按声明顺序排列的前\u003cem class=\"replaceable\"\u003e\u003ccode\u003eN\u003c/code\u003e\u003c/em\u003e个列名。\u003ccode class=\"literal\"\u003eVALUES\u003c/code\u003e子句或\u003cem class=\"replaceable\"\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e 提供的值，会按从左到右的顺序与显式或隐式列列表对应起来。\u003c/p\u003e\u003cp\u003e任何未出现在显式或隐式列列表中的列都会填入默认值；如果没有声明默认值，则填入空值。\u003c/p\u003e\u003cp\u003e如果任何列的表达式数据类型不正确，将尝试自动进行类型转换。\u003c/p\u003e\u003cp\u003e对没有唯一索引的表执行\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e不会被并发活动阻塞。若并发会话执行的操作会锁定或修改与待插入唯一索引值匹配的行，则带有唯一索引的表可能发生阻塞；详见\u003ca href=\"/docs/18/index-unique-checks.html\" title=\"63.5. 索引唯一性检查\"\u003e第 63.5 节\u003c/a\u003e。\u003ccode class=\"literal\"\u003eON CONFLICT\u003c/code\u003e可用于指定一种替代动作，而不是报出违反唯一约束或排他约束的错误。（见下文\u003ca href=\"/docs/18/sql-insert.html#SQL-ON-CONFLICT\" title=\"ON CONFLICT 子句\"\u003eON CONFLICT Clause\u003c/a\u003e。）\u003c/p\u003e\u003cp\u003e可选的\u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e子句使\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e 基于每个实际插入的行（若使用了\u003ccode class=\"literal\"\u003eON CONFLICT DO UPDATE\u003c/code\u003e 子句，则也可能是更新后的行）计算并返回一个或多个值。这主要用于获取由默认值提供的值，例如 serial 序列号。不过，也允许使用任何引用该表列的表达式。\u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e列表的语法与 \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e的输出列表相同。只有成功插入或更新的行才会被返回。例如，如果某一行被锁定，但由于不满足 \u003ccode class=\"literal\"\u003eON CONFLICT DO UPDATE ... WHERE\u003c/code\u003e子句中的 \u003cem class=\"replaceable\"\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e而未被更新，则该行不会被返回。\u003c/p\u003e\u003cp\u003e要向表中插入行，必须具有该表上的\u003ccode class=\"literal\"\u003eINSERT\u003c/code\u003e权限。如果存在\u003ccode class=\"literal\"\u003eON CONFLICT DO UPDATE\u003c/code\u003e子句，还要求具有该表上的\u003ccode class=\"literal\"\u003eUPDATE\u003c/code\u003e权限。\u003c/p\u003e\u003cp\u003e如果指定了列列表，你只需要对所列出的列具有\u003ccode class=\"literal\"\u003eINSERT\u003c/code\u003e权限。类似地，在指定\u003ccode class=\"literal\"\u003eON CONFLICT DO UPDATE\u003c/code\u003e时，你只需要对列出要更新的列具有\u003ccode class=\"literal\"\u003eUPDATE\u003c/code\u003e权限。不过，所有形式的\u003ccode class=\"literal\"\u003eON CONFLICT\u003c/code\u003e还要求对任何值会被读取的列具有\u003ccode class=\"literal\"\u003eSELECT\u003c/code\u003e权限。这包括 \u003cem class=\"replaceable\"\u003e\u003ccode\u003econflict_target\u003c/code\u003e\u003c/em\u003e中提到的任何列（包括仲裁约束引用的列），以及\u003ccode class=\"literal\"\u003eON CONFLICT DO UPDATE\u003c/code\u003e的 \u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e或\u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e子句 \u003cem class=\"replaceable\"\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e中提到的任何列。\u003c/p\u003e\u003cp\u003e使用\u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e子句要求对 \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e中提到的所有列具有 \u003ccode class=\"literal\"\u003eSELECT\u003c/code\u003e权限。如果使用\u003cem class=\"replaceable\"\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e子句将查询结果中的行插入表中，那么当然还需要对查询中用到的任何表或列具有\u003ccode class=\"literal\"\u003eSELECT\u003c/code\u003e 权限。\u003c/p\u003e","key":"description","title":"描述"},{"html":"\u003cdiv class=\"refsect2\"\u003e\u003ch3\u003e插入\u003c/h3\u003e\u003cp\u003e本节介绍仅在插入新行时可用的参数。\u003cspan class=\"emphasis\"\u003e\u003cem\u003e专门用于\u003c/em\u003e\u003c/span\u003e \u003ccode class=\"literal\"\u003eON CONFLICT\u003c/code\u003e子句的参数将单独说明。\u003c/p\u003e\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ewith_query\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003eWITH\u003c/code\u003e子句允许指定一个或多个子查询，这些子查询可以在\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e查询中按名称引用。详见\u003ca href=\"/docs/18/queries-with.html\" title=\"7.8. WITH查询（公共表表达式）\"\u003e第 7.8 节\u003c/a\u003e和\u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\"\u003e\u003cspan class=\"refentrytitle\"\u003eSELECT\u003c/span\u003e\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e（\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e语句）本身也可以包含 \u003ccode class=\"literal\"\u003eWITH\u003c/code\u003e子句。在这种情况下，\u003cem class=\"replaceable\"\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e中可以引用两组 \u003cem class=\"replaceable\"\u003e\u003ccode\u003ewith_query\u003c/code\u003e\u003c/em\u003e，但由于第二组嵌套得更近，它具有更高的优先级。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e一个现有表的名称（可以是模式限定的）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ealias\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e的替代名称。提供别名后，它会完全隐藏表的实际名称。当 \u003ccode class=\"literal\"\u003eON CONFLICT DO UPDATE\u003c/code\u003e的目标是一个名为 \u003ccode class=\"varname\"\u003eexcluded\u003c/code\u003e的表时，这一点特别有用，因为否则该名称会被当作表示拟插入行的那个特殊表名。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e名为\u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e的表中某一列的名称。如有需要，列名可以附带子字段名或数组下标。（只向复合列的部分字段插入值时，其余字段将为空值。）在 \u003ccode class=\"literal\"\u003eON CONFLICT DO UPDATE\u003c/code\u003e中引用列时，不要在目标列的指定中包含表名。例如，\u003ccode class=\"literal\"\u003eINSERT INTO table_name ... ON CONFLICT DO UPDATE SET table_name.col = 1\u003c/code\u003e是无效的（这与\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e的一般行为一致）。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eOVERRIDING SYSTEM VALUE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e如果指定了此子句，那么为标识列提供的任何值都将覆盖默认的序列生成值。\u003c/p\u003e\u003cp\u003e对于定义为\u003ccode class=\"literal\"\u003eGENERATED ALWAYS\u003c/code\u003e的标识列，如果插入显式值（\u003ccode class=\"literal\"\u003eDEFAULT\u003c/code\u003e除外）而未指定 \u003ccode class=\"literal\"\u003eOVERRIDING SYSTEM VALUE\u003c/code\u003e或 \u003ccode class=\"literal\"\u003eOVERRIDING USER VALUE\u003c/code\u003e，则会报错。（对于定义为\u003ccode class=\"literal\"\u003eGENERATED BY DEFAULT\u003c/code\u003e的标识列，\u003ccode class=\"literal\"\u003eOVERRIDING SYSTEM VALUE\u003c/code\u003e本来就是默认行为，因而显式指定它不会产生任何效果，但 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e仍将其作为扩展允许使用。）\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eOVERRIDING USER VALUE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e如果指定了此子句，则会忽略为标识列提供的任何值，并应用默认的序列生成值。\u003c/p\u003e\u003cp\u003e例如，在表之间复制值时，这个子句很有用。写成 \u003ccode class=\"literal\"\u003eINSERT INTO tbl2 OVERRIDING USER VALUE SELECT * FROM tbl1\u003c/code\u003e将从\u003ccode class=\"literal\"\u003etbl1\u003c/code\u003e复制所有在 \u003ccode class=\"literal\"\u003etbl2\u003c/code\u003e中不是标识列的列，而 \u003ccode class=\"literal\"\u003etbl2\u003c/code\u003e中标识列的值将由与 \u003ccode class=\"literal\"\u003etbl2\u003c/code\u003e关联的序列生成。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eDEFAULT VALUES\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e所有列都会填入各自的默认值，就像为每一列都显式指定了 \u003ccode class=\"literal\"\u003eDEFAULT\u003c/code\u003e一样。（这种形式不允许出现 \u003ccode class=\"literal\"\u003eOVERRIDING\u003c/code\u003e子句。）\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e赋给相应列的表达式或值。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eDEFAULT\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e相应列将填入其默认值。标识列将填入由关联序列生成的新值。对于生成列，允许指定该关键字，但这只是表明采用根据其生成表达式计算该列这一正常行为。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e提供要插入行的查询（\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e语句）。其语法说明请参见\u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\"\u003e\u003cspan class=\"refentrytitle\"\u003eSELECT\u003c/span\u003e\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_alias\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e在\u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e列表中为\u003ccode class=\"literal\"\u003eOLD\u003c/code\u003e或 \u003ccode class=\"literal\"\u003eNEW\u003c/code\u003e行指定的可选替代名称。\u003c/p\u003e\u003cp\u003e默认情况下，可通过 \u003ccode class=\"literal\"\u003eOLD.\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e 或\u003ccode class=\"literal\"\u003eOLD.*\u003c/code\u003e返回目标表旧值，通过 \u003ccode class=\"literal\"\u003eNEW.\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e 或\u003ccode class=\"literal\"\u003eNEW.*\u003c/code\u003e返回新值。提供别名后，这些名称会被隐藏，必须使用别名来引用旧行或新行。例如 \u003ccode class=\"literal\"\u003eRETURNING WITH (OLD AS o, NEW AS n) o.*, n.*\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_expression\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e在每一行插入或更新后由\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e命令计算并返回的表达式。该表达式可以使用由\u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e指定的表中的任何列。写成\u003ccode class=\"literal\"\u003e*\u003c/code\u003e可返回插入或更新后行的全部列。\u003c/p\u003e\u003cp\u003e列名或\u003ccode class=\"literal\"\u003e*\u003c/code\u003e可以用\u003ccode class=\"literal\"\u003eOLD\u003c/code\u003e或 \u003ccode class=\"literal\"\u003eNEW\u003c/code\u003e进行限定，也可以用 \u003ccode class=\"literal\"\u003eOLD\u003c/code\u003e或\u003ccode class=\"literal\"\u003eNEW\u003c/code\u003e对应的 \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_alias\u003c/code\u003e\u003c/em\u003e进行限定，以返回旧值或新值。未限定的列名或\u003ccode class=\"literal\"\u003e*\u003c/code\u003e，以及使用目标表名或别名限定的列名或\u003ccode class=\"literal\"\u003e*\u003c/code\u003e，都会返回新值。\u003c/p\u003e\u003cp\u003e对于普通\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e，所有旧值都为 \u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e。但对于带 \u003ccode class=\"literal\"\u003eON CONFLICT DO UPDATE\u003c/code\u003e子句的 \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e，旧值可能为非\u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e用于返回列的名称。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/div\u003e\u003cdiv class=\"refsect2\"\u003e\u003ch3\u003e\u003ccode class=\"literal\"\u003eON CONFLICT\u003c/code\u003e 子句\u003c/h3\u003e\u003cp\u003e可选的\u003ccode class=\"literal\"\u003eON CONFLICT\u003c/code\u003e子句指定一种替代动作，用来替代抛出唯一约束或排他约束违背错误。对于每一条拟插入的行，要么插入继续进行；要么如果违反了由\u003cem class=\"parameter\"\u003e\u003ccode\u003econflict_target\u003c/code\u003e\u003c/em\u003e 指定的某个\u003cspan class=\"emphasis\"\u003e\u003cem\u003e仲裁\u003c/em\u003e\u003c/span\u003e约束或索引，就执行替代的 \u003cem class=\"parameter\"\u003e\u003ccode\u003econflict_action\u003c/code\u003e\u003c/em\u003e。\u003ccode class=\"literal\"\u003eON CONFLICT DO NOTHING\u003c/code\u003e的替代动作只是跳过该行的插入。\u003ccode class=\"literal\"\u003eON CONFLICT DO UPDATE\u003c/code\u003e的替代动作则是更新与拟插入行冲突的现有行。\u003c/p\u003e\u003cp\u003e\u003cem class=\"parameter\"\u003e\u003ccode\u003econflict_target\u003c/code\u003e\u003c/em\u003e可以进行\u003cspan class=\"emphasis\"\u003e\u003cem\u003e唯一索引推断\u003c/em\u003e\u003c/span\u003e。执行推断时，它由一个或多个 \u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_column_name\u003c/code\u003e\u003c/em\u003e列和/或\u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_expression\u003c/code\u003e\u003c/em\u003e 表达式，以及可选的\u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_predicate\u003c/code\u003e\u003c/em\u003e 组成。所有在不考虑顺序的情况下恰好包含 \u003cem class=\"parameter\"\u003e\u003ccode\u003econflict_target\u003c/code\u003e\u003c/em\u003e指定列/表达式的 \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e唯一索引，都会被推断（选中）为仲裁索引。如果指定了 \u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_predicate\u003c/code\u003e\u003c/em\u003e，则作为推断的进一步要求，候选仲裁索引还必须满足该谓词。注意，这意味着如果存在某个满足其他所有条件的非部分唯一索引（即没有谓词的唯一索引），那么该索引也会被推断出来（从而被\u003ccode class=\"literal\"\u003eON CONFLICT\u003c/code\u003e 使用）。如果推断尝试失败，则会报错。\u003c/p\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003eON CONFLICT DO UPDATE\u003c/code\u003e保证得到原子的 \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e或\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e结果；只要没有其他独立错误，即使在高并发下，也能保证结果是这两者之一。这也称为\u003cem class=\"firstterm\"\u003eUPSERT\u003c/em\u003e — \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003eUPDATE or INSERT\u003c/span\u003e”\u003c/span\u003e。\u003c/p\u003e\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003econflict_target\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e通过选择\u003cem class=\"firstterm\"\u003e仲裁索引\u003c/em\u003e来指定 \u003ccode class=\"literal\"\u003eON CONFLICT\u003c/code\u003e对哪些冲突采取替代动作。它要么执行\u003cspan class=\"emphasis\"\u003e\u003cem\u003e唯一索引推断\u003c/em\u003e\u003c/span\u003e，要么显式命名一个约束。对于\u003ccode class=\"literal\"\u003eON CONFLICT DO NOTHING\u003c/code\u003e，是否指定 \u003cem class=\"parameter\"\u003e\u003ccode\u003econflict_target\u003c/code\u003e\u003c/em\u003e是可选的；省略时，将处理与所有可用约束（以及唯一索引）的冲突。对于 \u003ccode class=\"literal\"\u003eON CONFLICT DO UPDATE\u003c/code\u003e，则\u003cspan class=\"emphasis\"\u003e\u003cem\u003e必须\u003c/em\u003e\u003c/span\u003e提供\u003cem class=\"parameter\"\u003e\u003ccode\u003econflict_target\u003c/code\u003e\u003c/em\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003econflict_action\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003cem class=\"parameter\"\u003e\u003ccode\u003econflict_action\u003c/code\u003e\u003c/em\u003e指定一个替代的 \u003ccode class=\"literal\"\u003eON CONFLICT\u003c/code\u003e动作。它可以是 \u003ccode class=\"literal\"\u003eDO NOTHING\u003c/code\u003e，也可以是 \u003ccode class=\"literal\"\u003eDO UPDATE\u003c/code\u003e子句，用以精确指定发生冲突时要执行的\u003ccode class=\"literal\"\u003eUPDATE\u003c/code\u003e动作细节。在 \u003ccode class=\"literal\"\u003eON CONFLICT DO UPDATE\u003c/code\u003e中，\u003ccode class=\"literal\"\u003eSET\u003c/code\u003e和\u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e子句既可以通过表名（或别名）访问现有行，也可以通过特殊表 \u003ccode class=\"varname\"\u003eexcluded\u003c/code\u003e访问拟插入的行。对于任何被读取的\u003ccode class=\"varname\"\u003eexcluded\u003c/code\u003e列，必须对目标表中的对应列具有\u003ccode class=\"literal\"\u003eSELECT\u003c/code\u003e权限。\u003c/p\u003e\u003cp\u003e注意，所有行级\u003ccode class=\"literal\"\u003eBEFORE INSERT\u003c/code\u003e触发器的效果都会反映在\u003ccode class=\"varname\"\u003eexcluded\u003c/code\u003e值中，因为这些效果可能促成该行被排除在插入之外。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_column_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e中某一列的名称。用于推断仲裁索引。遵循\u003ccode class=\"command\"\u003eCREATE INDEX\u003c/code\u003e 的格式。要求对\u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_column_name\u003c/code\u003e\u003c/em\u003e 具有\u003ccode class=\"literal\"\u003eSELECT\u003c/code\u003e权限。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_expression\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e与\u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_column_name\u003c/code\u003e\u003c/em\u003e 类似，但用于推断出现在索引定义中的、基于\u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e列的表达式（而非简单列）。遵循\u003ccode class=\"command\"\u003eCREATE INDEX\u003c/code\u003e的格式。要求对出现在\u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_expression\u003c/code\u003e\u003c/em\u003e 中的任何列具有\u003ccode class=\"literal\"\u003eSELECT\u003c/code\u003e权限。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecollation\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定时，要求相应的\u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_column_name\u003c/code\u003e\u003c/em\u003e或 \u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_expression\u003c/code\u003e\u003c/em\u003e 必须使用特定的排序规则，才能在推断时匹配。通常会省略，因为排序规则通常不会影响是否发生约束违背。遵循 \u003ccode class=\"command\"\u003eCREATE INDEX\u003c/code\u003e格式。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eopclass\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定时，要求相应的\u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_column_name\u003c/code\u003e\u003c/em\u003e或 \u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_expression\u003c/code\u003e\u003c/em\u003e 必须使用特定的操作符类，才能在推断时匹配。通常会省略，因为一种类型的各个操作符类在\u003cspan class=\"emphasis\"\u003e\u003cem\u003e相等性\u003c/em\u003e\u003c/span\u003e语义上往往是等价的，或者只需相信已定义的唯一索引具有所需的相等性定义即可。遵循\u003ccode class=\"command\"\u003eCREATE INDEX\u003c/code\u003e格式。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_predicate\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e用于允许推断部分唯一索引。任何满足该谓词的索引（实际上不一定是部分索引）都可以被推断。遵循\u003ccode class=\"command\"\u003eCREATE INDEX\u003c/code\u003e 格式。要求对出现在\u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_predicate\u003c/code\u003e\u003c/em\u003e 中的任何列具有\u003ccode class=\"literal\"\u003eSELECT\u003c/code\u003e权限。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003econstraint_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e显式按名称指定一个作为仲裁的\u003cspan class=\"emphasis\"\u003e\u003cem\u003e约束\u003c/em\u003e\u003c/span\u003e，而不是通过推断约束或索引。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e返回\u003ccode class=\"type\"\u003eboolean\u003c/code\u003e值的表达式。只有使该表达式返回 \u003ccode class=\"literal\"\u003etrue\u003c/code\u003e的行才会被更新，不过一旦采取 \u003ccode class=\"literal\"\u003eON CONFLICT DO UPDATE\u003c/code\u003e动作，所有行都会被锁定。请注意，只有在某个冲突已被识别为更新候选后，才会最后计算\u003cem class=\"replaceable\"\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003cp\u003e请注意，排他约束不支持在\u003ccode class=\"literal\"\u003eON CONFLICT DO UPDATE\u003c/code\u003e 中充当仲裁对象。在所有情况下，只有\u003ccode class=\"literal\"\u003eNOT DEFERRABLE\u003c/code\u003e 约束和唯一索引可作为仲裁对象。\u003c/p\u003e\u003cp\u003e带有\u003ccode class=\"literal\"\u003eON CONFLICT DO UPDATE\u003c/code\u003e子句的 \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e是一种\u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e确定性\u003c/span\u003e”\u003c/span\u003e语句。这意味着不允许该命令对任何单个现有行产生多于一次的影响；一旦出现这种情况，就会报出基数违背错误。拟插入的行在受仲裁索引或约束限制的属性上不应彼此重复。\u003c/p\u003e\u003cp\u003e请注意，目前尚不支持在针对分区表的\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e的 \u003ccode class=\"literal\"\u003eON CONFLICT DO UPDATE\u003c/code\u003e子句中更新冲突行的分区键，从而需要将该行移动到一个新分区。\u003c/p\u003e\u003cdiv class=\"tip\"\u003e\u003ch3\u003e提示\u003c/h3\u003e\u003cp\u003e通常更推荐使用唯一索引推断，而不是通过 \u003ccode class=\"literal\"\u003eON CONFLICT ON CONSTRAINT\u003c/code\u003e \u003cem class=\"replaceable\"\u003e\u003ccode\u003econstraint_name\u003c/code\u003e\u003c/em\u003e直接命名约束。当底层索引以重叠方式被另一个大致等价的索引替换时，推断仍能继续正确工作，例如在删除待替换索引之前先使用 \u003ccode class=\"literal\"\u003eCREATE UNIQUE INDEX ... CONCURRENTLY\u003c/code\u003e。\u003c/p\u003e\u003c/div\u003e\u003cdiv class=\"warning\"\u003e\u003ch3\u003e警告\u003c/h3\u003e\u003cp\u003e当某个唯一索引上正在执行 \u003ccode class=\"command\"\u003eCREATE INDEX CONCURRENTLY\u003c/code\u003e或 \u003ccode class=\"command\"\u003eREINDEX CONCURRENTLY\u003c/code\u003e时，同一张表上的 \u003ccode class=\"command\"\u003eINSERT ... ON CONFLICT\u003c/code\u003e语句可能会意外地因唯一性违反而失败。\u003c/p\u003e\u003c/div\u003e\u003c/div\u003e","key":"parameters","title":"参数"},{"html":"\u003cp\u003e成功完成时，\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e命令会返回如下形式的命令标签：\u003c/p\u003e\u003cpre class=\"screen\"\u003eINSERT \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoid\u003c/code\u003e\u003c/em\u003e \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\n\u003c/pre\u003e\u003cp\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e是插入或更新的行数。\u003cem class=\"replaceable\"\u003e\u003ccode\u003eoid\u003c/code\u003e\u003c/em\u003e始终为 0（过去，如果 \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e恰好为 1 且目标表声明为 \u003ccode class=\"literal\"\u003eWITH OIDS\u003c/code\u003e，则它是分配给插入行的 \u003cacronym\u003eOID\u003c/acronym\u003e，否则为 0；但现在已经不再支持创建 \u003ccode class=\"literal\"\u003eWITH OIDS\u003c/code\u003e表）。\u003c/p\u003e\u003cp\u003e如果\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e命令包含\u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e 子句，则结果将类似于一个\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e语句，其中包含 \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e列表定义的列和值，它们是基于该命令插入或更新的行计算出来的。\u003c/p\u003e","key":"outputs","title":"输出"},{"html":"\u003cp\u003e如果指定的表是分区表，则每一行都会被路由到适当的分区并插入其中。如果指定的表本身就是一个分区，则只要输入行中有任意一行违反该分区约束，就会报错。\u003c/p\u003e\u003cp\u003e也可以考虑使用\u003ccode class=\"command\"\u003eMERGE\u003c/code\u003e，因为它允许在单条语句中混合\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e、\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e和 \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e。参见\u003ca href=\"/docs/18/sql-merge.html\" title=\"MERGE\"\u003e\u003cspan class=\"refentrytitle\"\u003eMERGE\u003c/span\u003e\u003c/a\u003e。\u003c/p\u003e","key":"notes","title":"注解"},{"html":"\u003cp\u003e向表\u003ccode class=\"literal\"\u003efilms\u003c/code\u003e插入一行：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eINSERT INTO films VALUES\n    ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes');\n\u003c/pre\u003e\u003cp\u003e在这个示例中，省略了\u003ccode class=\"literal\"\u003elen\u003c/code\u003e列，因此它将取默认值：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eINSERT INTO films (code, title, did, date_prod, kind)\n    VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');\n\u003c/pre\u003e\u003cp\u003e这个示例对日期列使用\u003ccode class=\"literal\"\u003eDEFAULT\u003c/code\u003e子句，而不是显式指定值：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eINSERT INTO films VALUES\n    ('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes');\nINSERT INTO films (code, title, did, date_prod, kind)\n    VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama');\n\u003c/pre\u003e\u003cp\u003e要插入一行，其所有列都由默认值构成：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eINSERT INTO films DEFAULT VALUES;\n\u003c/pre\u003e\u003cp\u003e使用多行\u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e语法插入多行：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eINSERT INTO films (code, title, did, date_prod, kind) VALUES\n    ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),\n    ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');\n\u003c/pre\u003e\u003cp\u003e这个示例从与\u003ccode class=\"literal\"\u003efilms\u003c/code\u003e列布局相同的 \u003ccode class=\"literal\"\u003etmp_films\u003c/code\u003e表中选出一些行并插入到 \u003ccode class=\"literal\"\u003efilms\u003c/code\u003e表：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eINSERT INTO films SELECT * FROM tmp_films WHERE date_prod \u0026lt; '2004-05-07';\n\u003c/pre\u003e\u003cp\u003e这个示例向数组列插入值：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003e-- 为井字棋创建一个空的 3x3 棋盘\nINSERT INTO tictactoe (game, board[1:3][1:3])\n    VALUES (1, '{{\" \",\" \",\" \"},{\" \",\" \",\" \"},{\" \",\" \",\" \"}}');\n-- 上例中的下标实际上并非必需\nINSERT INTO tictactoe (game, board)\n    VALUES (2, '{{X,\" \",\" \"},{\" \",O,\" \"},{\" \",X,\" \"}}');\n\u003c/pre\u003e\u003cp\u003e向表\u003ccode class=\"literal\"\u003edistributors\u003c/code\u003e插入一行，并返回由 \u003ccode class=\"literal\"\u003eDEFAULT\u003c/code\u003e子句生成的序号：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eINSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')\n   RETURNING did;\n\u003c/pre\u003e\u003cp\u003e为负责 Acme Corporation 账户的销售人员增加销量计数，并把整个更新后的行连同当前时间记录到日志表中：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eWITH upd AS (\n  UPDATE employees SET sales_count = sales_count + 1 WHERE id =\n    (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation')\n    RETURNING *\n)\nINSERT INTO employees_log SELECT *, current_timestamp FROM upd;\n\u003c/pre\u003e\u003cp\u003e视情况插入或更新新的分销商。假设已经定义了一个唯一索引，用于约束出现在\u003ccode class=\"literal\"\u003edid\u003c/code\u003e列中的值。请注意，特殊的 \u003ccode class=\"varname\"\u003eexcluded\u003c/code\u003e表用于引用最初拟插入的值：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eINSERT INTO distributors (did, dname)\n    VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')\n    ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;\n\u003c/pre\u003e\u003cp\u003e按照上例插入或更新新的分销商，并返回任何被更新的既有值以及插入的新数据。请注意，对于没有冲突的行，返回的\u003ccode class=\"literal\"\u003eold_did\u003c/code\u003e 和\u003ccode class=\"literal\"\u003eold_dname\u003c/code\u003e值将为\u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eINSERT INTO distributors (did, dname)\n    VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')\n    ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname\n    RETURNING old.did AS old_did, old.dname AS old_dname,\n              new.did AS new_did, new.dname AS new_dname;\n\u003c/pre\u003e\u003cp\u003e插入一个分销商；如果已存在一条会导致待插入行被排除的现有行（即在行级\u003ccode class=\"literal\"\u003eBEFORE INSERT\u003c/code\u003e触发器触发后，其受约束列与待插入行匹配的行），则对该待插入行不做任何操作。示例假设已经定义了一个唯一索引，用于约束出现在\u003ccode class=\"literal\"\u003edid\u003c/code\u003e列中的值：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eINSERT INTO distributors (did, dname) VALUES (7, 'Redline GmbH')\n    ON CONFLICT (did) DO NOTHING;\n\u003c/pre\u003e\u003cp\u003e视情况插入或更新新的分销商。示例假设已经定义了一个唯一索引，用于约束出现在\u003ccode class=\"literal\"\u003edid\u003c/code\u003e列中的值。这里使用 \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e子句来限制实际会被更新的行（不过，任何未更新的现有行仍会被锁定）：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003e-- 不更新位于某个邮政编码区域内的现有分销商\nINSERT INTO distributors AS d (did, dname) VALUES (8, 'Anvil Distribution')\n    ON CONFLICT (did) DO UPDATE\n    SET dname = EXCLUDED.dname || ' (formerly ' || d.dname || ')'\n    WHERE d.zipcode \u0026lt;\u0026gt; '21201';\n\n-- 在语句中直接指定约束名称（使用关联的\n-- 索引来仲裁是否采取 DO NOTHING 动作）\nINSERT INTO distributors (did, dname) VALUES (9, 'Antwerp Design')\n    ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING;\n\u003c/pre\u003e\u003cp\u003e如果可能就插入新的分销商；否则执行 \u003ccode class=\"literal\"\u003eDO NOTHING\u003c/code\u003e。示例假设已经定义了一个唯一索引，用于约束这样一个行子集上的\u003ccode class=\"literal\"\u003edid\u003c/code\u003e列值：其中 \u003ccode class=\"literal\"\u003eis_active\u003c/code\u003e布尔列计算结果为\u003ccode class=\"literal\"\u003etrue\u003c/code\u003e：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003e-- 这条语句可以推断出 \"did\" 上以 \"WHERE is_active\"\n-- 为谓词的部分唯一索引，但也可以直接使用\n-- \"did\" 上的普通唯一约束\nINSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')\n    ON CONFLICT (did) WHERE is_active DO NOTHING;\n\u003c/pre\u003e","key":"examples","title":"示例"},{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e符合 SQL 标准，但 \u003ccode class=\"literal\"\u003eRETURNING\u003c/code\u003e子句是 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e扩展，在 \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e中使用\u003ccode class=\"literal\"\u003eWITH\u003c/code\u003e的能力以及使用\u003ccode class=\"literal\"\u003eON CONFLICT\u003c/code\u003e指定替代动作的能力也都是扩展。此外，标准不允许省略列名列表却又不是所有列都由\u003ccode class=\"literal\"\u003eVALUES\u003c/code\u003e 子句或\u003cem class=\"replaceable\"\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e填充的情况。如果你希望使用比\u003ccode class=\"literal\"\u003eON CONFLICT\u003c/code\u003e更符合 SQL 标准的语句，请参见\u003ca href=\"/docs/18/sql-merge.html\" title=\"MERGE\"\u003e\u003cspan class=\"refentrytitle\"\u003eMERGE\u003c/span\u003e\u003c/a\u003e。\u003c/p\u003e\u003cp\u003eSQL 标准规定，只有当存在一个定义为总是生成值的标识列时，才能指定 \u003ccode class=\"literal\"\u003eOVERRIDING SYSTEM VALUE\u003c/code\u003e。而 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e在任何情况下都允许该子句，并在它不适用时忽略它。\u003c/p\u003e\u003cp\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e子句可能存在的限制见\u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\"\u003e\u003cspan class=\"refentrytitle\"\u003eSELECT\u003c/span\u003e\u003c/a\u003e。\u003c/p\u003e","key":"compatibility","title":"兼容性"}],"sections_same_as":"","synopsis_html":"[ WITH [ RECURSIVE ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003ewith_query\u003c/code\u003e\u003c/em\u003e [, ...] ]\nINSERT INTO \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e [ AS \u003cem class=\"replaceable\"\u003e\u003ccode\u003ealias\u003c/code\u003e\u003c/em\u003e ] [ ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e [, ...] ) ]\n    [ OVERRIDING { SYSTEM | USER } VALUE ]\n    { DEFAULT VALUES | VALUES ( { \u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e | DEFAULT } [, ...] ) [, ...] | \u003cem class=\"replaceable\"\u003e\u003ccode\u003equery\u003c/code\u003e\u003c/em\u003e }\n    [ ON CONFLICT [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003econflict_target\u003c/code\u003e\u003c/em\u003e ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003econflict_action\u003c/code\u003e\u003c/em\u003e ]\n    [ RETURNING [ WITH ( { OLD | NEW } AS \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_alias\u003c/code\u003e\u003c/em\u003e [, ...] ) ]\n                { * | \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_expression\u003c/code\u003e\u003c/em\u003e [ [ AS ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoutput_name\u003c/code\u003e\u003c/em\u003e ] } [, ...] ]\n\n\u003cspan class=\"phrase\"\u003e其中 \u003cem class=\"replaceable\"\u003e\u003ccode\u003econflict_target\u003c/code\u003e\u003c/em\u003e 可以是以下之一：\u003c/span\u003e\n\n    ( { \u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_column_name\u003c/code\u003e\u003c/em\u003e | ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_expression\u003c/code\u003e\u003c/em\u003e ) } [ COLLATE \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecollation\u003c/code\u003e\u003c/em\u003e ] [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eopclass\u003c/code\u003e\u003c/em\u003e ] [, ...] ) [ WHERE \u003cem class=\"replaceable\"\u003e\u003ccode\u003eindex_predicate\u003c/code\u003e\u003c/em\u003e ]\n    ON CONSTRAINT \u003cem class=\"replaceable\"\u003e\u003ccode\u003econstraint_name\u003c/code\u003e\u003c/em\u003e\n\n\u003cspan class=\"phrase\"\u003e其中 \u003cem class=\"replaceable\"\u003e\u003ccode\u003econflict_action\u003c/code\u003e\u003c/em\u003e 是以下之一：\u003c/span\u003e\n\n    DO NOTHING\n    DO UPDATE SET { \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e = { \u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e | DEFAULT } |\n                    ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e [, ...] ) = [ ROW ] ( { \u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e | DEFAULT } [, ...] ) |\n                    ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e [, ...] ) = ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003esub-SELECT\u003c/code\u003e\u003c/em\u003e )\n                  } [, ...]\n              [ WHERE \u003cem class=\"replaceable\"\u003e\u003ccode\u003econdition\u003c/code\u003e\u003c/em\u003e ]","synopsis_text":"[ WITH [ RECURSIVE ] with_query [, ...] ]\nINSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]\n[ OVERRIDING { SYSTEM | USER } VALUE ]\n{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }\n[ ON CONFLICT [ conflict_target ] conflict_action ]\n[ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ]\n{ * | output_expression [ [ AS ] output_name ] } [, ...] ]\n\n其中 conflict_target 可以是以下之一：\n\n( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]\nON CONSTRAINT constraint_name\n其中 conflict_action 是以下之一：\n\nDO NOTHING\nDO UPDATE SET { column_name = { expression | DEFAULT } |\n( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |\n( column_name [, ...] ) = ( sub-SELECT )\n} [, ...]\n[ WHERE condition ]"}},"RequestedLocale":"zh-Hans","Fallback":false,"Versions":["10","11","12","13","14","15","16","17","18","19","20","6.4","6.5","7.0","7.1","7.2","7.3","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6"],"Locales":["en","zh-Hans"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}