In this section: |
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.
Autocommit=0; insert into <psql_table> (<columns>) select <columns> from <hyperstage_table> …; commit;
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=BRIGHTHOUSE) 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:
Important: It is strongly recommended that you always include the ENGINE=<engine_name> in the CREATE TABLE statement.
WebFOCUS |