INSERT

INSERT — Create new rows in a table

Synopsis

[ WITH [ RECURSIVE ] with_query [, ...] ]

INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]

[ OVERRIDING { SYSTEM | USER } VALUE ]

{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...]
| query }

[ ON CONFLICT [ conflict_target ] conflict_action ]

[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

where conflict_target can be one of:

( { index_column_name | ( index_expression ) } [ COLLATE collation ]

[ opclass ] [, ...] ) [ WHERE index_predicate ]

ON CONSTRAINT constraint_name

and conflict_action is one of:

DO NOTHING

DO UPDATE SET { column_name = { expression | DEFAULT } |

( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT }

[, ...] ) |( column_name [, ...] ) = ( sub-SELECT )

} [, ...]

[ WHERE condition ]

Description

INSERT inserts new rows into a table. One or more rows specified by value expressions can be inserted, or zero or more rows resulting from a query.

The target column names can be listed in any order. If no column name list is given, there are two ways to determine the target columns. The first is to list all columns of the table in their declared order. The second possibility is that if the VALUES clause or query provides only N columns, the first N columns of the table in their declared order are used. The values provided by the VALUES clause or query are associated with the explicitly or implicitly given target columns from left to right.

Each column not present in the explicit or implicit column list will be filled with its default value if one has been declared for it, or with null otherwise.

If the expression for any column is not of the correct data type, automatic type conversion will be attempted.

ON CONFLICT can be used to specify an alternative action in case of a unique constraint or exclusion constraint violation error (see ON CONFLICT Clause below).

The optional RETURNING clause causes INSERT to compute and return values based on each row actually inserted (or updated, if the ON CONFLICT DO UPDATE clause is used). This is primarily useful for obtaining values supplied by defaults, such as a serial number. However, any expression using the table's columns is allowed. The syntax of the RETURNING list is identical to that of the output list of SELECT. Only rows successfully inserted or updated will be returned. For example, if a row is locked but not updated because it does not satisfy the ON CONFLICT DO UPDATE ... WHERE clause condition, the row will not be returned.

To insert into a table, you must have INSERT privilege on it. If an ON CONFLICT DO UPDATE clause is present, UPDATE privilege on the table is also required.

If a column list is specified, you only need INSERT privilege on those columns. Similarly, when ON CONFLICT DO UPDATE is specified, you only need UPDATE privilege on the columns listed for update. However, ON CONFLICT DO UPDATE also requires SELECT privilege on any columns whose values are used by the ON CONFLICT DO UPDATE expressions or condition.

Using the RETURNING clause requires SELECT privilege on all columns mentioned in RETURNING. If the query clause is used to insert rows from a query, SELECT privilege is of course required on any tables or columns used in the query.

Parameters

Inserting

This section describes the parameters available when inserting new rows only. Parameters specific to the ON CONFLICT clause are described separately.

with_query

The WITH clause allows specifying one or more subqueries that can be referenced by name within the INSERT query.

The query (SELECT statement) can also contain a WITH clause. In this case, both sets of with_query can be referenced within the query, but the second takes precedence (since it is nested more closely).

table_name

The name of an existing table (optionally schema-qualified).

alias

A substitute name for table_name. When an alias is provided, it completely hides the actual table name. This is particularly useful when the target of ON CONFLICT DO UPDATE is an excluded table, since that will be treated as the name of the special table representing rows to be inserted.

column_name

The name of a column in the table named table_name. If necessary, the column name can be qualified with a subfield name or array subscript (inserting into some columns of a composite column will leave the other fields null). When referencing a column with ON CONFLICT DO UPDATE, do not include the table name in a target column specification. For example, INSERT INTO table_name ... ON CONFLICT DO UPDATE SET table_name.col = 1 is invalid (this follows the general behavior of UPDATE).

OVERRIDING SYSTEM VALUE

If this clause is specified, then any value supplied for an identity column overrides the default sequence-generated value.

For identity columns defined as GENERATED ALWAYS, it is an error to insert an explicit value (other than DEFAULT) without specifying either OVERRIDING SYSTEM VALUE or OVERRIDING USER VALUE. (For identity columns defined as GENERATED BY DEFAULT, OVERRIDING SYSTEM VALUE is the normal behavior, and specifying it does nothing, but Halo allows it as an extension.)

OVERRIDING USER VALUE

If this clause is specified, then any value supplied for an identity column is ignored and the default sequence-generated value is applied.

For example, this clause can be useful when copying values between tables. INSERT INTO tbl2 OVERRIDING USER VALUE SELECT * FROM tbl1 will copy all columns from tbl1 that are not identity columns in tbl2, while the identity column values in tbl2 will be generated by the sequence associated with tbl2.

DEFAULT VALUES

All columns will be filled with their default values, as if DEFAULT had been explicitly specified for each column. (OVERRIDING clauses are not allowed in this form.)

expression

An expression or value to assign to the corresponding column.

DEFAULT

The corresponding column will be filled with its default value. Identity columns will be filled with a new value generated by the associated sequence. For generated columns, specifying this value is allowed but merely specifies the normal behavior of computing the column from its generation expression.

query

A query (SELECT statement) that supplies the rows to be inserted. See the SELECT statement for its syntax description.

output_expression

An expression to be computed and returned by the INSERT command after each row is inserted or updated. The expression can use any column from the table named by table_name. Write * to return all columns of the inserted or updated row.

output_name

A name to use for a returned column.

ON CONFLICT Clause

The optional ON CONFLICT clause provides an alternative action when a uniqueness violation or exclusion constraint violation error occurs. For each row to be inserted, either the insertion proceeds, or if an arbiter constraint or index specified by conflict_target is violated, the alternative conflict_action is taken. ON CONFLICT DO NOTHING simply avoids inserting the row. ON CONFLICT DO UPDATE updates the existing row that conflicts with the row to be inserted.

conflict_target can perform unique index inference. When performing inference, it consists of one or more index_column_name columns or index_expression expressions, and an optional index_predicate. All unique indexes on table_name that exactly contain the columns/expressions specified by conflict_target (regardless of order) will be inferred (selected) as arbiter indexes. If index_predicate is specified, it must also be satisfied by the arbiter index (this is an additional requirement during the inference process). Note that this means if a non-partial unique index (one without a predicate) satisfying the other conditions is available, it will be inferred as the arbiter (and will be used by ON CONFLICT). If the inference attempt is unsuccessful, an error will occur.

ON CONFLICT DO UPDATE guarantees an atomic INSERT or UPDATE outcome. Given no unrelated errors, one of these two outcomes is guaranteed even under high concurrency. This is also known as UPSERT — "UPDATE or INSERT".

conflict_target

Specifies which rows conflict with the rows on which ON CONFLICT takes an alternative action, by selecting the arbiter index. Either perform unique index inference, or explicitly name a constraint. For ON CONFLICT DO NOTHING, specifying a conflict_target is optional. When omitted, conflicts with all valid constraints (and unique indexes) are handled. For ON CONFLICT DO UPDATE, a conflict_target must be provided.

conflict_action

conflict_action specifies an alternative ON CONFLICT action. It can be DO NOTHING, or a DO UPDATE clause specifying the details of the UPDATE action to be performed in case of a conflict. The SET and WHERE clauses in ON CONFLICT DO UPDATE can use the table name (or alias) to access existing rows, and can use the special excluded table to access the row being inserted. This action requires SELECT privilege on any column of the target table where the excluded columns are located.

Note that the effects of all row-level BEFORE INSERT triggers are reflected in the excluded values, since those effects may cause the row to not be inserted.

index_column_name

The name of a column of table_name. It is used to infer the arbiter index. It follows the CREATE INDEX format. This requires SELECT privilege on index_column_name.

index_expression

Similar to index_column_name, but used to infer expressions on table_name columns (not simple columns) that appear in index definitions. It follows the CREATE INDEX format. This requires SELECT privilege on any columns appearing in index_expression.

collation

When specified, forces the corresponding index_column_name or index_expression to use a specific collation so that it can be matched during inference. It is usually omitted, since collations typically do not affect whether a constraint violation occurs. It follows the CREATE INDEX format.

opclass

When specified, forces the corresponding index_column_name or index_expression to use a specific operator class so that it can be matched during inference. It is usually omitted, since equality semantics are equivalent across operator classes of a type, or because it suffices to trust that the defined unique index has an appropriate equality definition. It follows the CREATE INDEX format.

index_predicate

Used to enable inference of partial unique indexes. Any index satisfying the predicate (not necessarily a partial index) can be inferred. It follows the CREATE INDEX format. This requires SELECT privilege on any columns appearing in index_predicate.

constraint_name

Explicitly specifies an arbiter constraint by name, rather than inferring a constraint or index.

condition

An expression that returns a boolean value. Only rows for which this expression returns true will be updated, though all rows will be locked when the ON CONFLICT DO UPDATE action is taken. Note that condition is evaluated last, i.e., after a conflict has been identified as a candidate for update.

Note that exclusion constraints are not supported as arbiters for ON CONFLICT DO UPDATE. In all cases, only NOT DEFERRABLE constraints and unique indexes are supported as arbiters.

An INSERT with an ON CONFLICT DO UPDATE clause is a "deterministic" statement. This means that the command is not allowed to affect any single existing row more than once; a cardinality violation error will occur if this happens. The rows to be inserted must not be duplicated on the attributes constrained by the arbiter index or constraint.

Note that currently, updating the partition key of a conflicting row with the ON CONFLICT DO UPDATE clause on a partitioned table is not supported, as this would cause the row to move to a new partition.

Output

On successful completion, the INSERT command returns a command tag of the form:

INSERT oid count

count is the number of rows inserted or updated. oid is always 0 (historically, if count was exactly 1 and the target table was declared WITH OIDS, it was the OID assigned to the inserted row, otherwise 0, but creating WITH OIDS tables is no longer supported).

If the INSERT command contains a RETURNING clause, the result will be similar to a SELECT statement with the columns and values defined in the RETURNING list, computed by the command on the inserted or updated rows.

Notes

If the specified table is a partitioned table, each row will be routed to the appropriate partition and inserted there. If the specified table is a partition, an error will occur if any of the input rows violate the partition's constraints.

Examples

-- Insert a single row into films:

INSERT INTO films VALUES

('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes');

-- In this example, the len column is omitted and thus will have its default value:

INSERT INTO films (code, title, did, date_prod, kind)

VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');

-- This example uses the DEFAULT clause for the date column instead of specifying a value:

INSERT INTO films VALUES

('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes');

INSERT INTO films (code, title, did, date_prod, kind)

VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama');

-- Insert a row consisting entirely of default values:

INSERT INTO films DEFAULT VALUES;

-- Insert multiple rows using multi-row VALUES syntax:

INSERT INTO films (code, title, did, date_prod, kind) VALUES

('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),

('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

-- This example inserts some rows from the table tmp_films into the table films, with both tables having the same column layout:

INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < '2004-05-07';

-- This example inserts into an array column:

-- Create an empty 3x3 board for a noughts-and-crosses game

INSERT INTO tictactoe (game, board[1:3][1:3])

VALUES (1, '{{" "," "," "},{" "," "," "},{" "," "," "}}');

-- In fact, the subscripts in the above example can be omitted:

INSERT INTO tictactoe (game, board)

VALUES (2, '{{X," "," "},{" ",O," "},{" ",X," "}}');

-- Insert a row into table distributors, returning the sequence number generated by the DEFAULT clause:

INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')

RETURNING did;

-- Increment the sales count of the salesperson managing the account for Acme Corporation, and log the entire updated row along with the current timestamp in a log table:

WITH upd AS (

UPDATE employees SET sales_count = sales_count + 1 WHERE id =

(SELECT sales_person FROM accounts WHERE name = 'Acme Corporation')

RETURNING * )

INSERT INTO employees_log SELECT *, current_timestamp FROM upd;

-- Insert or update a new distributor as appropriate. Assume a unique index has been defined to constrain the values appearing in the did column. Note that the special excluded table is used to reference the originally proposed values for insertion:

INSERT INTO distributors (did, dname)

VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')

ON CONFLICT (did) DO UPDATE SET dname =EXCLUDED.dname;

-- Insert a distributor, or do nothing if an excluded row (a row with a column matching the constraint or a column that would trigger a row-level before (or after) insert trigger) already exists. This example assumes a unique trigger has been defined to constrain the values appearing in the did column:

INSERT INTO distributors (did, dname) VALUES (7, 'Redline GmbH')

ON CONFLICT (did) DO NOTHING;

-- Insert or update a new distributor as appropriate. This example assumes a unique trigger has been defined to constrain the values appearing in the did column. The WHERE clause is used to limit which rows are actually updated (however, any existing rows that are not updated will still be locked):

-- Update distributors based on a specific ZIP code

INSERT INTO distributors AS d (did, dname) VALUES (8, 'Anvil Distribution')

ON CONFLICT (did) DO UPDATE

SET dname = EXCLUDED.dname || ' (formerly ' || d.dname || ')'

WHERE d.zipcode <> '21201';

-- Name a constraint directly in the statement (using the associated index to determine whether to perform the DO NOTHING action)

INSERT INTO distributors (did, dname) VALUES (9, 'Antwerp Design')

ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING;

-- Insert a new distributor if possible, otherwise DO NOTHING. This example assumes a unique index has been defined that constrains the values in the did column on the subset of rows where the is_active boolean column is true:

-- This statement might infer a partial unique index on "did" with predicate "WHERE is_active",
-- but it could also just use a regular unique constraint on "did"

INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')

ON CONFLICT (did) WHERE is_active DO NOTHING;