INSERT

Inserts values into all projections of the specified table.

Inserts values into all projections of the specified table. You must insert one complete tuple at a time. If no projections are associated with the target table, Vertica creates a superprojection to store the inserted values.

INSERT works for flex tables as well as regular native tables. If the table has real columns, inserted data of scalar types and native arrays of scalar types is added to both the real column and the __raw__ column. For data of complex types, the values are not added to the __raw__ column.

Syntax

INSERT [ /*+LABEL (label-string)*/ ] INTO [[database.]schema.]table-name 
    [ ( column-list ) ]
    { DEFAULT VALUES | VALUES ( values-list )[,...] | SELECT query-expression }

Parameters

LABEL

Assigns a label to a statement to identify it for profiling and debugging.

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

*table-name*
The target table. You cannot invoke INSERT on a projection.
*column-list*
A comma-delimited list of one or more target columns in this table, listed in any order. VALUES clause values are mapped to columns in the same order. If you omit this list, Vertica maps VALUES clause values to columns according to column order in the table definition.

A list of target columns is invalid with DEFAULT VALUES.

DEFAULT VALUES
Fills all columns with their default values as specified in the table definition. If no default value is specified for a column, Vertica inserts a NULL value.

You cannot specify a list of target columns with this option.

VALUES (values-list)
A comma-delimited list of one or more values to insert in the target columns, where each value is one of the following:

  • expression resolves to a value to insert in the target column. The expression must not nest other expressions, include Vertica meta-functions, or use mixed complex types. Values may include native array or ROW types if Vertica can coerce the element or field types.

  • DEFAULT inserts the default value as specified in the table definition.

If no value is supplied for a column, Vertica implicitly adds a DEFAULT value, if defined. Otherwise Vertica inserts a NULL value. If the column is defined as NOT NULL, INSERT returns an error.

You can use INSERT to insert multiple rows in the target table, by specifying multiple comma-delimited VALUES lists:

INSERT INTO table-name
   VALUES ( values-list ), ( values-list )[,...]

For details, see Multi-Row INSERT below.

SELECT query-expression
A query that returns the rows to insert. Isolation level applies only to the SELECT clauses and works like any query. Restrictions on use of complex types apply as in other queries.

Privileges

  • Table owner or user with GRANT OPTION is grantor

  • INSERT privilege on table

  • USAGE privilege on schema that contains the table

Committing successive table changes

Vertica follows the SQL-92 transaction model, so successive INSERT, UPDATE, and DELETE statements are included in the same transaction. You do not need to explicitly start this transaction; however, you must explicitly end it with COMMIT, or implicitly end it with COPY. Otherwise, Vertica discards all changes that were made within the transaction.

Multi-row INSERT

You can use INSERT to insert multiple rows in the target table, by specifying multiple comma-delimited VALUES lists. For example:

=> CREATE TABLE public.t1(a int, b int, c varchar(16));
CREATE TABLE
=> INSERT INTO t1 VALUES (1,2, 'un, deux'), (3,4, 'trois, quatre');
 OUTPUT
--------
      2
(1 row)

=> COMMIT;
COMMIT
=> SELECT * FROM t1;
 a | b |       c
---+---+---------------
 1 | 2 | un, deux
 3 | 4 | trois, quatre
(4 rows)

Restrictions

  • Vertica does not support subqueries as the target of an INSERT statement.
  • Restrictions on the use of complex types in SELECT statements apply equally to INSERT. Using complex values that cannot be coerced to the column type results in an error.

  • If primary key, unique key, or check constraints are enabled for automatic enforcement in the target table, Vertica enforces those constraints when you load new data. If a violation occurs, Vertica rolls back the operation and returns an error.

Examples

=> INSERT INTO t1 VALUES (101, 102, 103, 104);
=> INSERT INTO customer VALUES (10, 'male', 'DPR', 'MA', 35);
=> INSERT INTO start_time VALUES (12, 'film','05:10:00:01');
=> INSERT INTO retail.t1 (C0, C1) VALUES (1, 1001);
=> INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < '2004-05-07';

Vertica does not support subqueries or nested expressions as the target of an INSERT statement. For example, the following query returns an error message:

=> INSERT INTO t1 (col1, col2) VALUES ('abc', (SELECT mycolumn FROM mytable));
ERROR 4821:  Subqueries not allowed in target of insert

You can rewrite the above query as follows:

=> INSERT INTO t1 (col1, col2) (SELECT 'abc', mycolumn FROM mytable);
 OUTPUT
--------
      0
(1 row)

The following example shows how to use INSERT...VALUES with flex tables:

=> CREATE FLEX TABLE flex1();
 CREATE TABLE
=> INSERT INTO flex1(a,b) VALUES (1, 'x');
 OUTPUT
--------
      1
(1 row)

=> SELECT MapToString(__raw__) FROM flex1;
           MapToString
---------------------------------
{
"a" : "1",
"b" : "x"
}
(1 row)

The following example shows how to use INSERT...SELECT with flex tables:

=> CREATE FLEX TABLE flex2();
 CREATE TABLE
=> INSERT INTO flex2(a, b) SELECT a, b, '2016-08-10 11:10' c, 'Hello' d, 3.1415 e, f from flex1;
 OUTPUT
--------
      1
(1 row)

=> SELECT MapToString(__raw__) FROM flex2;
           MapToString
---------------------------------
{
"a" : "1",
"b" : "x",
"c" : "2016-08-10",
"d" : "Hello",
"e" : 3.1415,
"f" : null
}
(1 row)

The following examples use complex types:

=> CREATE TABLE inventory(storeID INT, product ROW(name VARCHAR, code VARCHAR));
CREATE TABLE

--- LookUpProducts() returns a row(varchar, int), which is cast to row(varchar, varchar):
=> INSERT INTO inventory(product) SELECT LookUpProducts();
 OUTPUT
--------
      5
(1 row)

--- Cannot use with select...values:
=> INSERT INTO inventory(product) VALUES(LookUpProducts());
ERROR 2631:  Column "product" is of type "row(varchar,varchar)" but expression is of type "row(varchar,int)"

--- Literal values are supported:
=> INSERT INTO inventory(product) VALUES(ROW('xbox',165));
 OUTPUT
--------
      1
(1 row)

=> SELECT product FROM inventory;
          product
------------------------------
 {"name":"xbox","code":"125"}
(1 row)