How to work with keyed tables
This page introduces keyed tables in q and shows how to create and work with them.
A KDB-X keyed table is not a table, it is a dictionary (has type 99h), where:
- Its key is a table of the key column(s)
- Its value is a table of the non-key columns
In table notation, write the key field(s) inside the square brackets. A key is a unique identifier, similar to a primary key in a traditional SQL database.
When constructing a table key, ensure its items are unique.
To protect performance, kdb+ does not ensure key items are unique.
There is no use case for duplicate key items, as it makes 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 alternatively using 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 '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 will fail. This is also true in q.
For further information on how to use foreign keys in q, refer to the Foreign Keys Guide.
Only primary keys of keyed tables can be used as a foreign key. But there are other ways to link table columns. For further information on how to use linked columns in q, refer to the Linking Columns Guide.
Indexing a keyed table
There are two ways to index a keyed table.
First, with a single row from its key, returning 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
// Get key values of keyed table
q)flip{x cols x}key kt
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, with a sublist from its key, returning a list of dictionaries, which is a table.
q)kt ([]eid:1001 1002;name:`Dent`Beeblebrox)
iq shortName
------------
98 DT
42 BX
Upsert
upsert syntax is as for insert above.
For a simple table (not keyed), 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)`table upsert ([] stock:`aapl; enlist (price:140.5 160.8 180.9))
`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 // data inserted
q)`table upsert ([] stock:`ibm; enlist (price:11.2 14.3 16.7))
`table
q)table
stock| price
-----| -----------------
intel| 123.2 120.4 131
ibm | 11.2 14.3 16.7 // data updated
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