INSERT
The INSERT command inserts values into the 
Write Optimized Store (WOS) for all projections of a table.
The WOS (Write Optimized Store) is a memory-resident data structure into which INSERT, UPDATE, DELETE, and COPY (without DIRECT hint) actions are recorded. Like the ROS, the WOS is arranged by projection but it stores tuples without sorting, compression, or indexing and thus supports very fast load speeds. The WOS organizes data by epoch and holds uncommitted transaction data.
Compression is the process of transforming data into a more compact format. Compressed data cannot be directly processed; it must first be decompressed. Vertica uses integer packing for unencoded integers and LZO for compressible data. Although compression is generally considered to be a form of encoding, the terms have different meanings in Vertica.
LZO is an abbreviation for Lempel-Ziv-Oberhumer. It is a data compression algorithm that is focused on decompression speed. The algorithm is lossless and the reference implementation is thread safe.
The ROS (Read Optimized Store) is a highly optimized, read-oriented, physical storage structure that is organized by projection and that makes heavy use of compression and indexing. You can use the COPY...DIRECT and INSERT (with direct hint) statements to load data directly into the ROS.
Compression is the process of transforming data into a more compact format. Compressed data cannot be directly processed; it must first be decompressed. Vertica uses integer packing for unencoded integers and LZO for compressible data. Although compression is generally considered to be a form of encoding, the terms have different meanings in Vertica.
LZO is an abbreviation for Lempel-Ziv-Oberhumer. It is a data compression algorithm that is focused on decompression speed. The algorithm is lossless and the reference implementation is thread safe.
Syntax
INSERT [ /*+ direct */ ] INTO table [ ( column [, ...] ) ]
{ DEFAULT VALUES |
VALUES ( { expression | DEFAULT } [, ...] ) | SELECT...
}
Semantics
/*+ direct */
|
writes the data directly to disk (ROS) instead of memory (WOS). This syntax is only valid when used with INSERT...SELECT.
The WOS (Write Optimized Store) is a memory-resident data structure into which INSERT, UPDATE, DELETE, and COPY (without DIRECT hint) actions are recorded. Like the ROS, the WOS is arranged by projection but it stores tuples without sorting, compression, or indexing and thus supports very fast load speeds. The WOS organizes data by epoch and holds uncommitted transaction data.
Compression is the process of transforming data into a more compact format. Compressed data cannot be directly processed; it must first be decompressed. Vertica uses integer packing for unencoded integers and LZO for compressible data. Although compression is generally considered to be a form of encoding, the terms have different meanings in Vertica.
LZO is an abbreviation for Lempel-Ziv-Oberhumer. It is a data compression algorithm that is focused on decompression speed. The algorithm is lossless and the reference implementation is thread safe.
The ROS (Read Optimized Store) is a highly optimized, read-oriented, physical storage structure that is organized by projection and that makes heavy use of compression and indexing. You can use the COPY...DIRECT and INSERT (with direct hint) statements to load data directly into the ROS.
Compression is the process of transforming data into a more compact format. Compressed data cannot be directly processed; it must first be decompressed. Vertica uses integer packing for unencoded integers and LZO for compressible data. Although compression is generally considered to be a form of encoding, the terms have different meanings in Vertica.
LZO is an abbreviation for Lempel-Ziv-Oberhumer. It is a data compression algorithm that is focused on decompression speed. The algorithm is lossless and the reference implementation is thread safe.
The ROS (Read Optimized Store) is a highly optimized, read-oriented, physical storage structure that is organized by projection and that makes heavy use of compression and indexing. You can use the COPY...DIRECT and INSERT (with direct hint) statements to load data directly into the ROS.
Compression is the process of transforming data into a more compact format. Compressed data cannot be directly processed; it must first be decompressed. Vertica uses integer packing for unencoded integers and LZO for compressible data. Although compression is generally considered to be a form of encoding, the terms have different meanings in Vertica.
LZO is an abbreviation for Lempel-Ziv-Oberhumer. It is a data compression algorithm that is focused on decompression speed. The algorithm is lossless and the reference implementation is thread safe.
|
table
|
specifies the name of a table in the schema. You cannot INSERT tuples into a projection.
|
column
|
specifies a column of the table.
|
DEFAULT VALUES
|
fills all columns with their default values as specified in CREATE TABLE.
|
VALUES
|
specifies a list of values to store in the correspond columns. If no value is supplied for a column, Vertica implicitly adds a DEFAULT value, if present. Otherwise Vertica inserts a NULL value or, if the column is defined as NOT NULL, returns an error.
|
expression
|
specifies a value to store in the corresponding column.
|
DEFAULT
|
stores the default value in the corresponding column.
|
SELECT...
|
specifies a query (SELECT statement) that supplies the rows to be inserted.
|
Notes
- An INSERT ... SELECT ... statement refers to tables in both its INSERT and SELECT clauses. Isolation level applies only to the SELECT clauses and work just like an normal query except that you cannot use AT EPOCH LATEST or AT TIME in an INSERT ... SELECT statement. Instead, use the SET TRANSACTION CHARACTERISTICS statement to set the isolation level to READ COMMITTED. This is necessary in order to use INSERT ... SELECT while the database is being loaded.
- You can list the target columns in any order. If no list of column names is given at all, the default is all the columns of the table in their declared order; or the first N column names, if there are only N columns supplied by the VALUES clause or query. The values supplied by the VALUES clause or query are associated with the explicit or implicit column list left-to-right.
- You must insert one complete tuple at a time.
- Be aware of WOS Overload.
Examples
INSERT INTO FACT VALUES (101, 102, 103, 104);
INSERT INTO CUSTOMER VALUES (10, 'male', 'DPR', 'MA', 35);
INSERT INTO T1 (C0, C1) VALUES (1, 1001);
INSERT INTO films
SELECT * FROM tmp_films
WHERE date_prod < '2004-05-07';
SQL Language References
PostgreSQL 8.0.12 Documentation
BNF Grammar for SQL-99
