How to work with keyed tables

This page introduces keyed tables in q and shows how to create and work with them.

A keyed table is not a table but a dictionary (type 99h), where:

• Its key is a table of one or more key columns.
• Its value is a table of the non-key columns.

In table notation, write the key fields inside the square brackets. A key is a unique identifier, similar to a primary key in a traditional SQL database.

Key uniqueness

When you construct a table key, make sure its items are unique. To protect performance, KDB-X does not check that key items are unique.

There is no use case for duplicate key items, as duplicates make operation results unpredictable.

Example:

q)show market:([name:`symbol$()] address:())
name| address
----| -------

You can also add a key to an existing table using the xkey keyword or the bang operator !:

q)show t:([]sym:`MSFT`AAPL`IBM;px:98 42 126)
sym  px
--------
MSFT 98
AAPL 42
IBM  126

q)show kt:`sym xkey t           / key table on the sym column
sym | px
----| ---
MSFT| 98
AAPL| 42
IBM | 126

q)key kt
sym
----
MSFT
AAPL
IBM

q)value kt
px
---
98
42
126

q)keys kt
,`sym

q)1!t                           / key table on 1st column
sym | px
----| ---
MSFT| 98
AAPL| 42
IBM | 126

q)0!(1!t)                       / unkey table
sym  px
--------
MSFT 98
AAPL 42
IBM  126

Foreign keys

A foreign key in SQL is a column in one table whose values are members of a primary key column in another table. Foreign keys are the mechanism for establishing relations between tables.

Foreign keys in SQL provide referential integrity: an attempt to insert a foreign key value that is not in the primary key fails. This is also true in q.

You can use only the primary key of a keyed table as a foreign key, but there are other ways to link table columns:

• Foreign keys
• Linking columns

Indexing a keyed table

There are two ways to index a keyed table.

First, index with a single row from the key, which returns a dictionary.

q)/ keyed table with single key
q)show kt:([eid:1001 1002 1003] name:`Dent`Beeblebrox`Prefect; iq:98 42 126)
eid | name       iq
----| --------------
1001| Dent       98
1002| Beeblebrox 42
1003| Prefect    126
q)flip {x cols x} key kt        / key values of the keyed table
1001
1002
1003

q)kt 1003
name| `Prefect
iq  | 126

q)/ keyed table with two keys
q)show kt:([eid:1001 1002 1003;name:`Dent`Beeblebrox`Prefect]; iq:98 42 126; shortName:`DT`BX`PT)
eid  name      | iq  shortName
---------------| -------------
1001 Dent      | 98  DT
1002 Beeblebrox| 42  BX
1003 Prefect   | 126 PT

q)flip {x cols x} key kt
1001 `Dent
1002 `Beeblebrox
1003 `Prefect

q)kt (1003;`Prefect)
iq       | 126
shortName| `PT

Second, index with a sublist from the key, which returns a list of dictionaries — that is, a table.

q)kt ([]eid:1001 1002;name:`Dent`Beeblebrox)
iq shortName
------------
98 DT
42 BX

Upserting records

upsert takes the same syntax as insert.

For an unkeyed table, upsert is equivalent to insert.

For a keyed table, it is an update if the key exists in the table, or an insert otherwise.

q)/ unkeyed table
q)show trade:([]stock:`ibm`bac`usb;price:121.3 5.76 8.19;amt:1000 500 800;time:09:03:06.000 09:03:23.000 09:04:01.000)
stock price amt  time
-----------------------------
ibm   121.3 1000 09:03:06.000
bac   5.76  500  09:03:23.000
usb   8.19  800  09:04:01.000
q)`trade insert (`ibm; 645.9; 200; 09:05:00.000)
,3
q)trade
stock price amt  time
-----------------------------
ibm   121.3 1000 09:03:06.000
bac   5.76  500  09:03:23.000
usb   8.19  800  09:04:01.000
ibm   645.9 200  09:05:00.000
q)`trade upsert (`ibm; 645.9; 200; 09:05:00.000)
`trade
q)trade
stock price amt  time
-----------------------------
ibm   121.3 1000 09:03:06.000
bac   5.76  500  09:03:23.000
usb   8.19  800  09:04:01.000
ibm   645.9 200  09:05:00.000
ibm   645.9 200  09:05:00.000

q)/ keyed table
q)show table:([stock:`intel`ibm] price:(123.2 120.4 131; 111.2 140.3 160.7))
stock| price
-----| -----------------
intel| 123.2 120.4 131
ibm  | 111.2 140.3 160.7

q)`table upsert ([stock:enlist `aapl] price:enlist 140.5 160.8 180.9)   / key absent, so insert
`table
q)table
stock| price
-----| -----------------
intel| 123.2 120.4 131
ibm  | 111.2 140.3 160.7
aapl | 140.5 160.8 180.9

q)`table upsert ([stock:enlist `ibm] price:enlist 11.2 14.3 16.7)       / key present, so update
`table
q)table
stock| price
-----| -----------------
intel| 123.2 120.4 131
ibm  | 11.2  14.3  16.7
aapl | 140.5 160.8 180.9

An alternative syntax for upsert is to use the ,: operator.

Example:

q)trade
stock price amt  time
-----------------------------
ibm   121.3 1000 09:03:06.000
bac   5.76  500  09:03:23.000
usb   8.19  800  09:04:01.000
ibm   645.9 200  09:05:00.000
ibm   645.9 200  09:05:00.000

q)trade ,: (`aapl; 122.5; 50; 09:04:59.000)
q)trade
stock price amt  time
-----------------------------
ibm   121.3 1000 09:03:06.000
bac   5.76  500  09:03:23.000
usb   8.19  800  09:04:01.000
ibm   645.9 200  09:05:00.000
ibm   645.9 200  09:05:00.000
aapl  122.5 50   09:04:59.000

Like insert, upsert can also take a table as an argument:

q)trade ,: ([] stock:`msft`msft; price:7.6 4.9; amt:300 700; time:09:06:00.000 09:07:59.000)
q)trade
stock price amt  time
-----------------------------
ibm   121.3 1000 09:03:06.000
bac   5.76  500  09:03:23.000
usb   8.19  800  09:04:01.000
ibm   645.9 200  09:05:00.000
ibm   645.9 200  09:05:00.000
aapl  122.5 50   09:04:59.000
msft  7.6   300  09:06:00.000
msft  4.9   700  09:07:59.000