Insert Rows

Insert data into Airport-managed tables using standard SQL INSERT statements with RETURNING clause support for generated columns.

The Airport extension supports inserting data into managed tables through standard SQL INSERT statements. The Arrow Flight server is responsible for storing and persisting the data.

Support for `RETURNING`

The Airport extension supports the RETURNING clause in INSERT statements.

From the DuckDB documentation:

The RETURNING clause may be used to return the contents of the rows that were inserted. This can be useful if some columns are calculated upon insert. For example, if the table contains an automatically incrementing primary key, then the RETURNING clause will include the automatically created primary key. This is also useful in the case of generated columns.

Example

A standard SQL INSERT statement is used:

-- Attach an Airport database
ATTACH 'example' (TYPE AIRPORT, location 'grpc://localhost:50312/');

-- Assume that there is a `main` schema
-- already in the `example` database
CREATE TABLE example.main.employees (
  name varchar,
  id integer
);

-- Insert a single row.
INSERT INTO example.main.employees values ('Rusty', 1);

Tip

Airport-managed tables lack the transactional guarantees of native DuckDB tables. When using the INSERT statement, all rows are sent to the Arrow Flight server where they are presumed to be immediately committed to storage. This differs from a standard SQL transaction that doesn’t commit data until COMMIT or ROLLBACK is issued.

The current philosophy of the Airport extension is to send the rows to the server. If the server fails to commit the rows during the RPC request, it will raise an Arrow Flight exception, causing the DuckDB transaction to abort.

This may change in the future.

Arrow Flight Server Implementation Notes

A DoExchange Arrow Flight RPC is made to insert rows. This allows the server to return rows (needed for the RETURNING clause), which may differ from the input.

Supplied gRPC Headers for `DoExchange` request.

Header Name	Description
`airport-operation`	Set to `insert` to indicate the operation being performed
`return-chunks`	Set to `1` if a `RETURNING` clause is present; otherwise, `0`

Schema Handling

The schema sent by the client must match the schema of the table.
The schema returned by the server should also match the table’s schema.

Final Metadata Message

When the DuckDB client finishes writing to the write stream of the DoExchange RPC, the server is expected to return a single metadata message on the read stream that is serialized using msgpack with this structure.

struct AirportChangedFinalMetadata
{
  uint64_t total_changed;
  MSGPACK_DEFINE_MAP(total_changed)
};

This message informs the client of the total number of rows successfully inserted to the table.

Support for RETURNING