Using INSERT, UPDATE, and DELETE

DML statements INSERT, UPDATE, and DELETE perform the same functions that they do in any ACID-compliant database:

  • INSERT loads individual rows into physical memory or loads a table using an INSERT AS SELECT clause.
  • UPDATE and DELETE modify existing data.

By default, INSERT, UPDATE, and DELETE statements write data to the WOS. If the WOS is full, these statements write data directly to the ROS. However, if you use the TRICKLE keyword, if the WOS is full, Vertica reports an error and rolls back the entire data load.

For large INSERT or UPDATE operations, use the DIRECT hint. This hint forces Vertica to load all data directly to ROS. For performance reasons, Vertica does not recommend loading large number of rows as single row inserts. Instead, use COPY.

Vertica differs from traditional databases in two ways:

  • DELETE does not actually delete data from disk storage; it marks rows as deleted so they are available for historical queries.
  • UPDATE writes two rows: one with new data and one marked for deletion.

You can intermix INSERT, UPDATE, and DELETE operations. Vertica follows the SQL-92 transaction model. You do not have to explicitly start a transaction, but you must use a COMMIT or ROLLBACK command (or COPY) to end a transaction. If you cancel a DML statement, Vertica rolls back the statement.