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 database 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`	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

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.
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 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)