Appending Data to Splayed Tables in KDB+/q

Introduction :-

In KDB+/q, appending data to a table is a common operation, but it works differently with splayed tables compared to regular tables. By default, methods like insert or upsert won’t work for splayed tables as they are structured differently. A typical insert operation like:

`table_name insert (col1_value; col2_value; col3_value)

is not applicable to splayed tables because of the way data is physically stored. Splayed tables are optimized for time-series data and store each column in separate files, which requires a specialized approach for appending new rows.

How to Insert a Row into a Splayed Table in KDB+/q :-

Since a splayed table stores each column as a separate file on disk, we cannot use the usual insert or upsert directly.

Instead, we use a file-based insert method that writes the new data directly into the underlying directory structure of the splayed table.

Correct Syntax to Insert a Row into a Splayed Table

To insert into a splayed table, use the following pattern:

`:table_name/ insert .Q.en[`:.;]([] col1:value1; col2:value2; col3:value3)

Let’s break this down:

• :table_name/ – The path to your splayed table (with a trailing slash to indicate a directory).
• .Q.en[:.; table]` – Encodes the table in a format suitable for writing to disk.
• ([] col1:val1; col2:val2; ...) – This is a single-row keyed table used as the row to insert.

Important Note on char Columns

If you’re inserting into a column of type char (i.e., a string column), you must enlist the value. This is because a string like "Chennai" is treated as a list of characters in q unless explicitly enclosed.

Without enlist, "Chennai" would be interpreted as:

"Chennai" ~ ("C"; "h"; "e"; "n"; "n"; "a"; "i")

Which will not match the expected single string per row in a char column.

Example :-

Here’s a full example of inserting a row into a splayed table named table1 with multiple data types:

`:table_name/ insert .Q.en[`:.;]([] sno:7; name:`Naruto; fixed_date:2003.04.03; place:enlist "Chennai")

• sno is an integer.
• name is a symbol.
• fixed_date is a date.
• place is a char (string), so we use enlist.

This method safely writes the new row to disk in the correct format for a splayed table.

Preview Before Insert: Safe Way to Insert into a Splayed Table :-

When inserting directly into a splayed table using:

`:table_name/ insert .Q.en[`:.; ([] ...)]

you’re modifying the on-disk table immediately. While this is efficient, it’s also risky — if you make a mistake (wrong data type, unexpected nulls, typos), the change is already made and harder to undo.

Get Sathish Vadivel’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

To avoid that, you can use a preview-then-commit approach. This involves:

1. First simulating the insert in memory using a join.
2. Verifying the result.
3. Writing it back to the splayed table only if you’re satisfied.

Step 1: Append In-Memory Using , Join

You can use the comma join operator , to combine a new row with the existing splayed table.

✅ Table Format (with enlist for char):

table_name, ([] sno:7; name:`Naruto; fixed_date:2003.04.03; place:enlist "Chennai")

✅ Dictionary Format (no enlist needed for char):

table_name, (`sno`name`fixed_date`place!(7; `Naruto; 2003.04.03; "Chennai"))

This does not change anything on disk. It only creates a new in-memory table with the new row appended. You can check the result, validate the structure, preview the data, and only move forward if everything looks correct.

Step 2: Assign Back to the Variable

If everything looks good, assign the updated table back:

table_name: table_name, ([] sno:7; name:`Naruto; fixed_date:2003.04.03; place:enlist "Chennai")

Step 3: Persist to Disk (Splayed Table)

Finally, write the updated table back to the splayed folder:

`:table_name/ set .Q.en[`:.; table_name]

This safely writes your validated table to disk.

Conclusion :-

Inserting data into splayed tables in KDB+/q isn’t as straightforward as with in-memory tables. The default insert method doesn't apply, so we need to use .Q.en with proper formatting, especially for types like char. For safer updates, previewing the insert in-memory using a join allows you to verify data before committing changes. This reduces the risk of mistakes and ensures your splayed tables remain consistent. Always validate your data and structure before writing to disk for better reliability.