INSERT

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.

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.

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.

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 INSERT, UPDATE, and DELETE

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.

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. For example, literals for combinations of ROW and ARRAY types are not supported, and thus are not supported in INSERT...SELECT and INSERT...VALUES. Using complex values that cannot be coerced to the column type results in an error.

  • If any primary key, unique key, or check constraints are enabled for automatic enforcement, Vertica enforces those constraints when you insert values into a table. If a violation occurs, Vertica rolls back the SQL statement and returns an error. This behavior occurs for INSERT, UPDATE, COPY, and MERGE SQL statements.

    Automatic constraint enforcement requires that you have SELECT privileges on the table containing the constraint.

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)