Hyperstage > Data Manipulation Statements > INSERT

INSERT

Topics:

Hyperstage supports the INSERT statement. For more information, see the PostgreSQL 9.2 Documentation. 

INSERT [LOW_PRIORITY|DELAYED|HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    {VALUES|VALUE} ({expr|DEFAULT},...),(...),...

Important: To use INSERT in bulk or batch load, you must set AUTOCOMMIT=0 and explicitly use COMMIT to complete the transaction. If AUTOCOMMIT=1, then each insert will result in the decompression and recompression of data packs, causing very slow performance. Explicit commits ensure that compression is only done once.

Inserting a Query Result in a PostgreSQL Table

  • You can use the INSERT command to insert the result of a Hyperstage query into a PostgreSQL table. Enter the following command:
    Autocommit=0;
insert into <psql_table> (<columns>) select <columns> from <hyperstage_table> …;
commit;
  • The following example shows the use of the INSERT command as described above:
    psql> drop table if exists temp;
Query OK, 0 rows affected (0.00 sec)
psql> create table temp (sums int);
Query OK, 0 rows affected (0.00 sec)
psql> insert into temp (sums) select sum(i1) from tint;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0
psql> select * from temp;
+-----------+
| sums      |
+-----------+
| 87        |
+-----------+
1 row in set (0.00 sec)

The CREATE TABLE statement can be used in combination with a select statement to generate a series of INSERTs from one table into another. The format is as follows: 

CREATE TABLE <table_name> with (ENGINE=INFOBRIGHT) AS (SELECT …);

This will result in the creation and population of a new table based on the SELECT criteria. There are a few things to look out for:

  • Although the ENGINE=<engine_name> is optional, if not specified it will default to the default ENGINE for the database.

    Important: It is strongly recommended that you always include the ENGINE=<engine_name> in the CREATE TABLE statement.

  • If the SELECT contains functions (for example, CONCAT), the output field attributes will be based on the defined output of the function. In the case of concat, the field attribute will be aggregate of the field sizes defined in the concat.
  • The target column names, where functions are used, will be the function name unless an alias is provided:
    • Concat(a, b) will result in a target column name of Concat(a, b).
    • Concat(a, b) as c will result in a target column name of c.

WebFOCUS

Feedback