INSERT

Create a new row in the table.

Summary

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table [( column [, ...] )]
   {DEFAULT VALUES | VALUES ( {expression | DEFAULT} [, ...] ) [, ...] | query}
   [ON CONFLICT [conflict_target] conflict_action]
   [RETURNING * | output_expression [[AS] output_name] [, ...]]

其中 conflict_target 可以是以下之一：

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

且 conflict_action 是以下之一：

    DO NOTHING
    DO UPDATE SET { column_name = { expression | DEFAULT } |
                    ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
                    ( column_name [, ...] ) = ( sub-SELECT )
                  } [, ...]
              [ WHERE condition ]

describe

INSERT Insert new rows into the table. One or more rows specified by the value expression can be inserted, or zero or more rows generated by a query.

Target column names can be listed in any order. If no list of column names is given at all, the columns in the table are arranged in declared order. The value provided by the VALUES clause or query is associated with an explicit or implicit column list from left to right.

Each column that does not exist in an explicit or implicit column list will be filled with a default value that can be its declared default value, or NULL if there is no default value.

If the data types of expressions for any column are incorrect, automatic type conversion will be attempted.

YMatrix supports the syntax of INSERT INTO ... VALUES ... ON CONFLICT ... DO ... to support UPSERT operations on HEAP table data. Restrictions: When using the UPSERT function, the unique constraint or index for table creation must contain a distribution key. At the same time, UPSERT does not support moving data between different instances and does not support updating distribution keys. For more information about using UPSERT, see Data Batch Merge Scenario (UPSERT). This statement applies only to HEAP tables.

The optional RETURNING clause causes INSERT to calculate and return a value based on each line actually inserted. This is useful for getting the default provided value (such as the serial number). However, any expressions of table columns are allowed. The syntax of the RETURNING list is the same as the syntax of the output list of SELECT.

You must have INSERT privilege on the table to be inserted into the table. After specifying a list of columns, you only need to have INSERT privileges on the listed columns. Using the RETURNING clause requires that all columns mentioned in RETURNING have SELECT privileges. If a query is provided to insert rows in the query, you must have SELECT privileges for any table or column referenced in the query.

Notes!
UPSERT is not a SQL statement, but an update/insert operation supported in a timing scenario to facilitate batch upload of data.

Output

After successful completion, the INSERT command returns the following command mark:

INSERT oid count

count is the number of inserted rows. If count happens to be 1 and the target table has OID, then OID is the OID assigned to the insert row. Otherwise, OID is zero.

Parameters

insert

with_query

The WITH clause allows you to specify one or more subqueries that can be referenced by name in the INSERT query.
For INSERT commands containing WITH clause, this clause can only contain SELECT statements, while WITH clause cannot contain data modification commands (INSERT, UPDATE or DELETE).
Query (SELECT statement) may also contain a WITH clause. In this case, two sets of with_query can be referenced in the INSERT query, but the second set takes precedence because it has a tighter nesting.

table

The name of the existing table (can be qualified by Schema).

column

Name of the column in the table. If desired, you can use subfield names or array subscripts to qualify column names. (Insert only into some fields in the composite column, the fields that are not specified are empty.)

DEFAULT VALUES

All columns will be populated with their default values.

Expression

The expression or value to assign to the corresponding column.

DEFAULT

The corresponding column will be filled with its default values.

query

Provides a query (SELECT statement) for the row to be inserted. For a description of the syntax, see the SELECT statement.

output_expression

The expression calculated and returned by the INSERT command after each line is inserted. This expression can use any column name of the table. Write * Returns all columns of the inserted row.

output_name

The name of the column used to return.

ON CONFLICT clause

ON CONFLICT DO UPDATE guaranteees an atomic INSERT or UPDATE result. Without irrelevant errors, one of these two results can be guaranteed, even at very high concurrency. This can also be called UPSERT — “UPDATE or INSERT”.

conflict_target

Specify which rows conflict with the row on which ON CONFLICT take an alternative action. Either perform unique index inference or explicitly name a constraint. For ON CONFLICT DO NOTHING, it is optional for specifying a conflict_target. 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 a replaceable ON CONFLICT action. It can be DO NOTHING or a DO UPDATE clause that specifies the details of the UPDATE action to be executed in a conflicting situation. The SET and WHERE clause in ON CONFLICT DO UPDATE can access existing rows using the name (or alias) of the table, and can access rows to be inserted using a special excluded table. This action requires SELECT privileges on any column in the target table where the column is located.

Note that the effects of all row-level BEFORE INSERT triggers are reflected in the excluded values, as those effects may prevent the row from being inserted.

Note

To insert data into a partition table, specify the root partition table, which is a table created using the CREATE TABLE command. You can also specify the leaf table of the partition table in the INSERT command. If the data is invalid for the specified leaf table, an error is returned. Specifying subtables that are not leaf tables in the INSERT command is not supported. Execution of other DML commands such as UPDATE and DELETE on any subtables of partitioned tables is not supported. These commands must be executed on the root partition table (the table created using the CREATE TABLE command).

Example

Insert a row in the table films:

INSERT INTO films VALUES ('UA502', 'Bananas', 105, 
'1971-07-13', 'Comedy', '82 minutes');

In this example, the length column is omitted, so it will have a 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_prod column instead of specifying a value:

INSERT INTO films VALUES ('UA502', 'Bananas', 105, DEFAULT, 
'Comedy', '82 minutes');

To insert a line consisting entirely of default values:

INSERT INTO films DEFAULT VALUES;

To insert multiple lines using the 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 films into the table films with the same column layout as films:

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

Insert a row in the table distributors to return the sequence number generated by the DEFAULT clause:

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

Implement UPSERT in HEAP table:

// Create a test table
CREATE TABLE conditions (
  tag_id      text  NOT NULL,
  ts          timestamptz   not null,
  location    text              ,
  temperature double precision  ,
  humidity    double precision
) DISTRIBUTED BY (tag_id);
// Create a unique index
CREATE UNIQUE INDEX ON conditions(tag_id,ts);
// Insert test data
INSERT INTO conditions(tag_id,ts,location) VALUES('tag1','2022-07-19 00:00:00','office') ON CONFLICT(tag_id,ts) DO UPDATE
SET  location = excluded.location,temperature = excluded.temperature,humidity = excluded.humidity;
INSERT INTO conditions(tag_id,ts,temperature) VALUES('tag1','2022-07-19 00:00:00',70.2) ON CONFLICT(tag_id,ts) DO UPDATE
SET  location = excluded.location,temperature = excluded.temperature,humidity = excluded.humidity;
INSERT INTO conditions(tag_id,ts,humidity) VALUES('tag1','2022-07-19 00:00:00',50.1) ON CONFLICT(tag_id,ts) DO UPDATE
SET  location = excluded.location,temperature = excluded.temperature,humidity = excluded.humidity;
// View test data
mydb=# SELECT * FROM conditions;
 tag_id |           ts           | location | temperature | humidity
--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 tag1   | 2022-07-19 00:00:00+08 | office   |             |
(1 row)
Time: 16.340 ms

compatibility

INSERT complies with SQL standards. The standard does not allow column name lists to be omitted, but not all columns are popularized by the VALUES clause or query.

SELECT records possible limitations for the query clause.