How to work with tables
This page provides an introduction on how to work with tables in KDB-X.
Tables are first-class objects in q, meaning they are data structures that live in memory just like lists or dictionaries.
Tables are built from dictionaries, so please refresh your knowledge as required before proceeding.
Overview
- Syntax
- Metadata
- Empty tables and schema
- Querying
- Reading from disk
- Keyed tables
- Foreign keys
- Table indexing
- Amending tables
- List operations on tables
Syntax
Construct a small table using table notation:
([] c1:l1;c2:l2;...;cn:ln)
Where:
c1,..,cnare column namesl1,..,lnare the corresponding list of column values
Example:
q)show t:([]city:`Istanbul`Moscow`London;country:`Turkey`Russia`UK;pop:15067724 12615279 9126366)
city country pop
-----------------------------
Istanbul Turkey 15067724
Moscow Russia 12615279
London UK 9126366
Unlike classical relational databases, q tables are ordered - you can index into them. A table is an ordered list of same-key dictionaries.
q)t 2
city | `London
country| `UK
pop | 9126366
q)t 2 0
city country pop
-------------------------
London UK 9126366
Istanbul Turkey 15067724
q)first t
city | `Istanbul
country| `Turkey
pop | 15067724
q)last t
city | `London
country| `UK
pop | 9126366
Another way to construct a table is to flip a column dictionary.
Example:
q)show colDict:`city`country`pop!(`Berlin`Kyiv`Madrid;`Germany`Ukraine`Spain;3748148 3703100 3223334)
city | Berlin Kyiv Madrid
country| Germany Ukraine Spain
pop | 3748148 3703100 3223334
q)flip colDict
city country pop
----------------------
Berlin Germany 3748148
Kyiv Ukraine 3703100
Madrid Spain 3223334
q)type flip colDict
98h
Both table notation and the flip of a column dictionary yield the same resulting table:
q)t:([]sym:`MSFT`AAPL`IBM;px:98 42 126)
q)t~flip `sym`px!(`MSFT`AAPL`IBM;98 42 126)
1b
Metadata
The function meta applied to a table retrieves its metadata. The result is a keyed table with one record for each column in the original table:
- The key column
cof the result contains the column names. - The column
tcontains a symbol denoting the type char of the column. - The column
fcontains the domains of any foreign key or link columns. - The column
acontains any attributes associated with the column.
q)meta t
c | t f a
---| -----
sym| s
px | j
When meta displays an upper-case type char for a column, this indicates that column is a compound list in which all fields are simple lists of the indicated type.
Observe the upper case J in the t column for column c2:
q)show t:([]c1:1 2 3;c2:(1 2; enlist 3; 4 5 6))
c1 c2
--------
1 1 2
2 ,3
3 4 5 6
q)meta t
c | t f a
--| -----
c1| j
c2| J
Empty tables and schema
An empty table can be created by initializing each column as the general empty list:
q)meta trade:([] stock:(); price:(); amount:(); time:())
c | t f a
------| -----
stock |
price |
amount|
time |
In this table, the datatype of each column is mixed. The first record then inserted into the table sets the datatypes of the columns. Subsequent inserts may only insert values of the same type; otherwise a type error is signalled, which can be trapped and handled.
This requires the table to start with the correct column types, so it is often better to initialize a table with empty columns of the correct type. This ensures only data of the appropriate type is appended.
Example:
q)meta trade:([] stock:`$(); price:`float$(); `long$amount:(); `time$time:())
c | t f a
------| -----
stock | s
price | f
amount| j
time | t
Querying
For guides on querying, please refer to the querying section of the website, most notably; Query Data with qSQL for querying tables.
Reading from disk
To load a CSV as a table, please refer to Load CSV. To load data from text files, please refer to File Text.
For reading tables persisted to the filesystem, see Database: persisting tables in the filesystem.
Keyed tables
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, please 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, please refer to the Linking Columns Guide.
Table indexing
Column indexing
Since a table is a flipped column dictionary, we can also index the table by column names:
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 `stock`amt
ibm bac usb
1000 500 800
Row indexing
Selecting the ith row in a table is complex in SQL, but easy in q.
The pseudo column i represents the row index, that can be used in queries.
The select expression returns a table with a single row.
q)select from trade where i=2
stock price amt time
----------------------------
usb 8.19 800 09:04:01.000
Indexing a table with an atom returns a dictionary.
q)trade[2]
stock| `usb
price| 8.19
amt | 800
time | 09:04:01.000
Row indexing cannot be used on a keyed table, which is a dictionary.
It is also easy to access, say, the second-to-last row.
q)trade[(count trade) - 2]
stock| `bac
price| 5.76
amt | 500
time | 09:03:23.000
Here we can see the dual nature of a table. It is both:
- a list of named same-length columns
- a list of like (same-key) dictionaries
And we can index it either way – or both, which is indexing at depth.
q)trade 1 / index by row
stock| `bac
price| 5.76
amt | 500
time | 09:03:23.000
q)trade `price / index by column
121.3 5.76 8.19
q)trade[1 0;`stock`amt] / index at depth
`bac 500
`ibm 1000
Its items are dictionaries and, as a table is a list of like dictionaries, any sublist of the table is – also a table.
q)trade 1 0
stock price amt time
-----------------------------
bac 5.76 500 09:03:23.000
ibm 121.3 1000 09:03:06.000
Indexing at depth
Indexing at depth can be used to read a column within a specific row.
q)trade[2;`stock]
`usb
It's also useful for updates.
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
q)trade[2;`amt]:15
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 15 09:04:01.000
Index out of range
If we use select, the result is a table with no rows.
q)select from trade where i = 300000
stock price amt time
--------------------
If we use indexing, the result is a dictionary containing null values.
q)trade 30000
stock| `
price| 0n
amt | 0N
time | 0Nt
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
Amending tables
Inserting records
insert syntax:
`trade insert (`ibm; 1001; 122.5; 500; 09:04:59:000)
insert [`trade] (`ibm; 1001; 122.5; 500; 09:04:59:000)
insert [`trade; (`ibm; 1001; 122.5; 500; 09:04:59:000)]
Example:
q)table:([stock:()] price:())
q)insert[`table; (`intel; enlist (123.2; 120.4; 131.0))]
,0
q)table
stock| price
-----| ---------------
intel| 123.2 120.4 131
Bulk insert
The right argument to insert above is a list.
It can also be a table having the same column names as the first argument:
q)`table insert ([] stock:`ibm; enlist (price:111.2 140.3 160.7))
,1
q)table
stock| price
-----| -----------------
intel| 123.2 120.4 131
ibm | 111.2 140.3 160.7
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
Delete rows
To delete rows in a table, use the delete keyword:
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
q)trade: delete from trade where stock=`ibm
q)trade
stock price amt time
----------------------------
bac 5.76 500 09:03:23.000
usb 8.19 800 09:04:01.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
delete returns a table, but does not modify the trade table in place. The assignment above accomplishes that.
To update the table in place, use backtick:
q)trade
stock price amt time
----------------------------
bac 5.76 500 09:03:23.000
usb 8.19 800 09:04:01.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
q)delete from `trade where stock=`msft
`trade
q)trade
stock price amt time
----------------------------
bac 5.76 500 09:03:23.000
usb 8.19 800 09:04:01.000
aapl 122.5 50 09:04:59.000
Update values
To add or amend rows or columns in a table, use the update keyword.
In SQL:
UPDATE trade SET amount=42+amount WHERE stock='ibm'
Equivalent in q:
q)trade
stock price amt time
----------------------------
bac 5.76 500 09:03:23.000
usb 8.19 800 09:04:01.000
aapl 122.5 50 09:04:59.000
q)trade: update amt:42+amt from trade where stock=`usb
q)trade
stock price amt time
----------------------------
bac 5.76 500 09:03:23.000
usb 8.19 842 09:04:01.000
aapl 122.5 50 09:04:59.000
update returns a table, but does not modify the underlying table. The assignment above accomplishes that.
Alternatively, update modifies the table in place much like delete deletes in place if a symbol is given as the table name:
q)update amt+42 from `trade where stock=`bac
`trade
q)trade
stock price amt time
----------------------------
bac 5.76 542 09:03:23.000
usb 8.19 842 09:04:01.000
aapl 122.5 50 09:04:59.000
Replace null values
To replace null values in a table, use the fill operator ^.
For example, the following replaces all nulls in column amt with zeroes:
q)trade
stock price amt time
----------------------------
bac 5.76 542 09:03:23.000
usb 8.19 09:04:01.000
aapl 122.5 50 09:04:59.000
q)update amt:0^amt from trade
stock price amt time
----------------------------
bac 5.76 542 09:03:23.000
usb 8.19 0 09:04:01.000
aapl 122.5 50 09:04:59.000
List operations on tables
Because a table is an ordered list, many list operators work.
Take and Drop
For the head and tail of a table use the take operator #.
To drop records, use the drop operator _.
q)trade
stock price amt time
----------------------------
bac 5.76 542 09:03:23.000
usb 8.19 09:04:01.000
aapl 122.5 50 09:04:59.000
q)2#trade / take first two records
stock price amt time
----------------------------
bac 5.76 542 09:03:23.000
usb 8.19 09:04:01.000
q)-2#trade / take last two records
stock price amt time
----------------------------
usb 8.19 09:04:01.000
aapl 122.5 50 09:04:59.000
q)-2 _ trade / drop last two records
stock price amt time
----------------------------
bac 5.76 542 09:03:23.000
Note that take treats a list as circular if the number of items to take is longer than the list.
q)7#2 3 5
2 3 5 2 3 5 2
An alternative is to use sublist, which takes only as many rows as are available.
q)trade
stock price amt time
----------------------------
bac 5.76 542 09:03:23.000
usb 8.19 09:04:01.000
aapl 122.5 50 09:04:59.000
q)count trade
3
q)count 2 sublist trade
2
q)2 sublist trade
stock price amt time
----------------------------
bac 5.76 542 09:03:23.000
usb 8.19 09:04:01.000
q)count 30 sublist trade
3
You can also take selected columns:
q)`price`amt#trade
price amt
---------
5.76 542
8.19
122.5 50
Join and Join Each
The join operator , concatenates two lists – and tables are lists.
The table columns need not be in the same order.
q)trade
stock price amt time
----------------------------
bac 5.76 542 09:03:23.000
usb 8.19 09:04:01.000
aapl 122.5 50 09:04:59.000
q)trade,([] stock:`msft`aapl; price:17.5 103.2; amt:1500 750; time:10:32:17.000 10:35:45.000)
stock price amt time
-----------------------------
bac 5.76 542 09:03:23.000
usb 8.19 09:04:01.000
aapl 122.5 50 09:04:59.000
msft 17.5 1500 10:32:17.000
aapl 103.2 750 10:35:45.000
Join Each joins pairs of dictionaries and so has upsert semantics:
q)trade
stock price amt time
----------------------------
bac 5.76 542 09:03:23.000
usb 8.19 09:04:01.000
aapl 122.5 50 09:04:59.000
q)trade,'([] year:2019+til 3; exch:3?`NYSE`LSE)
stock price amt time year exch
--------------------------------------
bac 5.76 542 09:03:23.000 2019 LSE
usb 8.19 09:04:01.000 2020 NYSE
aapl 122.5 50 09:04:59.000 2021 LSE
For further discussion on joins please refer here.
List alternatives to queries
Many qSQL queries are equivalent to simple list operations.
For example:
select from trade where i=5 / trade[5]
select stock,amt from trade / `stock`amt#trade
select from trade where stock=`ibm / trade where `ibm=trade`stock
Summary
In this guide, you learned how to:
- Construct tables using table syntax and by flipping column dictionaries
- Create empty tables and schemas
- Query tables via qSQL
- Read data from disk into table format
- Create and work with keyed tables
- Use foreign keys and link columns
- Index simple and keyed tables
- Amend and alter tables
- Apply list operations to tables
For additional detail on tables, check out Q for Mortals §8. Tables
You now have the essential skills to work with tables in KDB-X.