Write data into tables by using the INSERT statement - Hologres

Use the INSERT statement to write rows into a Hologres table. Two write methods are supported: inserting explicit values with INSERT INTO ... VALUES, and inserting query results with INSERT INTO ... SELECT.

Syntax

INSERT INTO <schema>.<table> [( <column> [, ...] )]
    VALUES ( {<expression>} [, ...] ) [, ...]
    | <query>

Parameter	Description
`schema`	The name of the schema that contains the target table.
`table`	The name of the target table. When using Realtime Compute for Apache Flink to write data to Hologres, you can specify a parent table — data is automatically routed to the matching child table. In Hologres V1.3 and later, you can also use an INSERT statement with Fixed Plan syntax to write directly to a parent table. For details, see Accelerate SQL execution with Fixed Plan.
`column`	The name of the target column. Qualify with a subfield name or array subscript as needed. If you write to only some fields of a composite column, the remaining fields are set to null.
`expression`	The value or expression to assign to the corresponding column.
`query`	A SELECT statement that provides the rows to insert. See SELECT.

How it works

INSERT writes data to write-ahead logs (WAL) in Append Only mode and updates the memory table in real time, making data immediately visible after each write.

The memory table has a fixed capacity. When it fills up, Hologres switches to a new memory table and triggers an asynchronous flush to persistent storage. During the flush, a large number of small files are written to Pangu. Hologres then merges and sorts these files in the background — a process called compaction.

To maximize write throughput, Hologres defers compression and sorting to the compaction phase. As a result, storage usage climbs during active writes and drops again after compaction completes.

The flush format depends on the table's storage model:

Storage model	File format
Row-oriented	SST
Column-oriented	ORC
Row-column hybrid	SST + ORC (a write is confirmed only after both succeed)

Row-column hybrid tables store data in both formats, which increases storage overhead compared to row-oriented or column-oriented tables.

Write performance tips

The write mechanism described above has practical implications:

Use a Fixed Plan for parallel workloads: Running multiple INSERT statements in parallel on the same table without a Fixed Plan causes table-level locking. Use a Fixed Plan to eliminate lock contention and improve throughput. See Accelerate SQL execution with Fixed Plan.
Expect temporary storage spikes: Storage usage increases significantly during active writes and normalizes after compaction. This is expected behavior, not a data issue.

Write data

Insert explicit values

Use INSERT INTO ... VALUES to insert one or more rows with literal values.

INSERT INTO holo2mysqltest (cate_id, cate_name) VALUES
    (3, 'comedy'),
    (3, 'drama'),
    (4, 'The Dinner Game');

Insert from a query

Use INSERT INTO ... SELECT to copy rows from another table or query result.

INSERT INTO target_table
SELECT * FROM source_table;

The column order in SELECT must match the column list in INSERT (or the table's column order if no column list is specified). Data types must be compatible.

Examples

Insert rows into an internal table

Create a table and insert a row with values of various data types.

CREATE TABLE holotest (
    a   int,
    b   bigint,
    c   bool,
    e   decimal(38,10),
    f   text,
    g   timestamp,
    h   timestamptz,
    i   jsonb,
    j   int[]
);

INSERT INTO holotest VALUES (
    1,
    9223372036854775807,
    false,
    123.123456789123,
    'john',
    '2020-01-01 01:01:01.123456',
    '2004-10-19 10:23:54+08',
    '{"a":2}',
    ARRAY[1, 2, 3, 4]
);

-- Verify the inserted row
SELECT * FROM holotest;

Copy rows between tables

CREATE TABLE holotest2 (
    a int,
    b bigint,
    c bool
);

INSERT INTO holotest2 (a, b, c)
SELECT a, b, c FROM holotest;

-- Verify
SELECT * FROM holotest2;

Insert into a partitioned table

Create a partitioned parent table with child tables, then insert into a specific child table.

BEGIN;

CREATE TABLE public.hologres_parent (
    a text,
    b int,
    c timestamp,
    d text
) PARTITION BY LIST(a);

CALL set_table_property('public.hologres_parent', 'orientation', 'column');

CREATE TABLE public.hologres_2022 PARTITION OF public.hologres_parent FOR VALUES IN ('2022');
CREATE TABLE public.hologres_2021 PARTITION OF public.hologres_parent FOR VALUES IN ('2021');
CREATE TABLE public.hologres_2020 PARTITION OF public.hologres_parent FOR VALUES IN ('2020');

COMMIT;

-- Insert into a specific child table
INSERT INTO public.hologres_2022 VALUES ('2022', 1, now(), 'a');

-- Verify
SELECT * FROM public.hologres_2022;

Usage notes

Column order: Column names in the INSERT statement can be listed in any order. For INSERT INTO ... SELECT, the data types of the selected columns must match the types of the corresponding target columns.
Partitioned tables: When inserting into a parent table, the value of the partition key in each row must match the partition key value of an existing child table. Direct writes to a parent table are not supported unless you use Fixed Plan syntax (Hologres V1.3 and later).
Parallel inserts: Running multiple INSERT statements in parallel on the same table without a Fixed Plan causes table-level locking. Each statement waits for the previous lock to release, increasing latency. Use a Fixed Plan to avoid this.
Storage usage: Storage usage increases significantly during active writes and drops after compaction completes. This is expected behavior — see How it works for details.

Troubleshooting

`ERROR: no partition of relation "<table_name>" found for row`

No child table exists for the partition key value in the row you are inserting. Create the matching child table first:

CREATE TABLE <child_table_name> PARTITION OF <parent_table_name>
    FOR VALUES IN (<value>);

`Currently inserting into parent table is not supported`

The target table is a parent table. Hologres does not support writing data directly to a parent table. Insert into a specific child table instead.

What's next

Accelerate SQL execution with Fixed Plan — eliminate table-level locking and improve write throughput for parallel INSERT workloads
SELECT — query syntax for use with INSERT INTO ... SELECT
INSERT ON CONFLICT — use UPSERT semantics to insert or update rows based on conflict resolution