INSERT INTO

Adds rows to the table. If you try to insert a row into a table with an existing primary key value, the operation fails with the PRECONDITION_FAILED error code and the Operation aborted due to constraint violation: insert_pk message returned.

INSERT INTO lets you perform the following operations:

• Adding constant values using VALUES.

  INSERT INTO my_table (Key1, Key2, Value1, Value2)
  VALUES (345987,'ydb', 'Pied piper', 1414);
  COMMIT;
  

  INSERT INTO my_table (key, value)
  VALUES ("foo", 1), ("bar", 2);
  

• Saving the SELECT result.

  INSERT INTO my_table
  SELECT Key AS Key1, "Empty" AS Key2, Value AS Value1
  FROM my_table1;
  

When working with external file data sources, you can specify additional parameters:

• FORMAT — stored data format in file storage for federated queries. Allowed values: csv_with_names, tsv_with_names, json_list, json_each_row, json_as_string, parquet, raw.
• COMPRESSION — file compression in file storage for federated queries. Allowed values: gzip, zstd, lz4, brotli, bzip2, xz.
• PARTITIONED_BY — list of partition columns for data in file storage in federated queries. Lists columns in the order they appear in the file layout.
• projection.enabled — flag to enable extended data partitioning. Allowed values: true, false.
• projection.<field_name>.type — field type for extended data partitioning. Allowed values: integer, enum, date.
• projection.<field_name>.<options> — extended properties of a field for extended data partitioning.

Example

INSERT INTO `connection`.`test/`
WITH
(
  FORMAT = "csv_with_names"
)
SELECT
    "value" AS value, "name" AS name

Where:

• connection — name of the connection to S3 (Yandex Object Storage).
• test/ — path inside the bucket where data is written. Files are created with random names.

INSERT INTO ... RETURNING

Inserts rows and returns their values in a single operation. It allows to retrieve data from the rows being inserted without needing to perform a separate SELECT query afterwards.

Examples

• Return all values of modified rows

INSERT INTO some_table (id, year, color, price)
VALUES (1103, 2023, 'blue', 400)
RETURNING *;

• Return specific columns

INSERT INTO some_table (id, color, price)
VALUES 
    (1101, 'red', 200),
    (1102, 'green', 300)
RETURNING id, price;