Inserting data into flex tables
You can load data into an OpenText™ Analytics Database flex table using a standard INSERT statement, specifying data for one or more columns. When you use INSERT, the database populates any materialized columns and stores the VMap data in the __raw__ column.
Following are two ways to use INSERT with flex tables:
-
INSERT ... VALUES
-
INSERT ... SELECT
Inserting values into flex tables
To insert data values into a flex table, use an INSERT ... VALUES statement. If you do not specify any columns in your INSERT ... VALUES statement, the database positionally assigns values to the real columns of the flex table.
This example shows two ways to insert values into a simple flex table. For both statements, the database assigns the values 1 and 'x' to columns a and b, respectively.This example inserts values into the two real columns defined in the flex table:
=> CREATE FLEX TABLE flex0 (a INT, b VARCHAR);
CREATE TABLE
=> INSERT INTO flex0 VALUES (1, 'x');
OUTPUT
--------
1
(1 row)
This example inserts values into a flex table without any real columns:
=> CREATE FLEX TABLE flex1();
CREATE TABLE
=> INSERT INTO flex1(a,b) VALUES (1, 'x');
OUTPUT
--------
1
(1 row)
For the preceding example, the __raw__ column contains the inserted data:
=> SELECT MapToString(__raw__) FROM flex1;
MapToString
---------------------------------
{
"a" : "1",
"b" : "x"
}
(1 row)
Using INSERT ... SELECT with flex tables
Using an INSERT ... SELECT statement with a flex table is similar to using INSERT ... SELECT with a regular table. The SELECT statement returns the data to insert into the target table.
However, you are not required to balance the number of columns and values. If you do not specify a value for a column, NULL is inserted by default.
In the next example, the database copies the a and b values from the flex1 table, and creates columns c, d, e, and f. The statement does not specify a value for f, so it is assigned a NULL by default.
=> 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 11:10",
"d" : "Hello",
"e" : "3.1415",
"f" : null
}
(1 row)
Inserting __raw__ columns into a flex table
Inserting a __raw__ column into a flex table inserts the entire source VMap into the target table. The __raw__ column is not assigned to any target column. Its position in the SELECT statement does not matter.
The following two INSERT statements are equivalent.
=> INSERT INTO flex4(a,b) SELECT a, __raw__, b FROM flex3;
=> INSERT INTO flex4(a,b) SELECT a, b, __raw__ FROM flex3;
Error handling
Type coercion errors occur only with real columns. The insert operation fails as follows:
=> CREATE FLEX TABLE my_table(a INT, b VARCHAR);
CREATE TABLE
=> INSERT INTO my_table(a, b) VALUES ('xyz', '5');
ERROR: Invalid input syntax for integer: "xyz"
If you try to insert values into the __raw__ column, the insert fails as follows:
=> CREATE FLEX TABLE my_table(a INT, b VARCHAR);
CREATE TABLE
=> INSERT INTO my_table(a,b,__raw__) VALUES (1,'x','abcdef');
ERROR 7372: Cannot assign value to "__raw__" column