INSERT

Inserts values into all projections of the specified table. You must insert one complete tuple at a time. For databases created in Vertica 9.3 and later, INSERT writes directly to ROS. Databases created in earlier versions use a default load of AUTO.

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 database tables.

Syntax

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

Parameters

/*+ hint[, hint] */

One or both of the following hints:

For databases created in Vertica releases ≥ 9.3, Vertica ignores load options and hints and defaults to a load method of DIRECT. You can configure this behavior with configuration parameter DMLTargetDirect.

[database.]schema

Specifies a schema, by default public. If schema is any schema other than public, you must supply the schema name. For example:

myschema.thisDbObject

If you specify a database, it must be the current database.

table‑name

The target table. You cannot invoke INSERT on a projection. This can be a flex table.

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 or include Vertica meta-functions.
  • 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

Specifies a query that returns the rows to insert. Isolation level applies only to the SELECT clauses and works like any query.

Privileges

  • Table owner or user with GRANT OPTION is grantor
  • INSERT privilege on table
  • USAGE privilege on schema that contains the table

Restrictions

  • Vertica does not support subqueries as the target of an INSERT statement.
  • 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 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 next 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)