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 myschema.thisDbObject If you specify a database, it must be the current database. |
table‑name
|
The target table. You cannot invoke |
column‑list
|
A comma-delimited list of one or more target columns in this table, listed in any order. A list of target columns is invalid with |
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:
If no value is supplied for a column, Vertica implicitly adds a |
SELECT query‑expression
|
Specifies a query that returns the rows to insert. Isolation level applies only to the |
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
, andMERGE
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)