Tables form the basis of a kdb+ database and are consequently one of the most important data structures in q. This tutorial deals with creating and manipulating tables, including inserts and upserts. Tables II is about queries and joins.
Tables: keyed and unkeyed
We saw in Tutorials/Dictionaries how to create a table by flipping a dictionary. There is another (perhaps simpler) syntax for defining tables.
q)family:(name:`John`Mary`David; age:52 49 18; hair:`brown`black`blonde; eyes:`blue`brown`blue) q)family name age hair eyes ------------------------ John 52 brown blue Mary 49 black brown David 18 blonde blue q)type family 98h q)(a:1 2 3;b:4 5 6 7) / columns must be the same length 'length
The table above was defined within parentheses, with columns separated by a semicolon. The square brackets at the beginning are empty because the table is unkeyed. Unkeyed tables have a type of 98h. So what is a keyed table? We can define some of the columns within the square brackets, which give the following result:
q)keyedtab:([a:`f`y`i] b:3 4 5; c:6 7 8) q)keyedtab a| b c -| --- f| 3 6 y| 4 7 i| 5 8 q)type keyedtab 99h
See the vertical line separating the first column from the others, similar to how dictionaries are displayed? Notice too that the type of the keyed table is 99h – just like dictionaries!
A keyed table is a dictionary whose keys and values are both tables.
Keyed and unkeyed tables act differently in different situations, which we will see later, so it is important to be able to key and unkey tables. There are two syntaxes for this:
q)2!family / keys on the first 2 columns name age| hair eyes -----------| ------------ John 52 | brown blue Mary 49 | black brown David 18 | blonde blue q)3!family / keys on the first 3 columns name age hair | eyes ------------------| ----- John 52 brown | blue Mary 49 black | brown David 18 blonde| blue q)`age`eyes xkey family / specifies columns to key on age eyes | name hair ---------| -------------- 52 blue | John brown 49 brown| Mary black 18 blue | David blonde
Creating an unkeyed table from a keyed one uses exactly the same syntaxes, you just 'key on nothing'.
q)0!keyedtab a b c ----- f 3 6 y 4 7 i 5 8 q)() xkey keyedtab / () is an empty list a b c ----- f 3 6 y 4 7 i 5 8
In the q/kdb+ world, 'indexing' usually refers to accessing elements of lists or other structures,not speeding up access, which is done using attributes.
Unkeyed tables are like lists: rows are numbered starting with 0.
q)family 0 2 name age hair eyes --------------------- John 52 brown blue David 18 blonde blue q)family 1 name| `Mary age | 49 hair| `black eyes| `brown
Notice that a single row is returned as a dictionary, but multiple rows make a table. (A table is a list of dictionaries.)
Keyed tables are dictionaries, and so their rows are indexed by keys instead of numbers.
q)keyedtab`y b| 4 c| 7
We can also access the columns of an unkeyed table as if it were a dictionary:
Inserts and upserts
q)`family insert (`Jessica;13;`black;`blue) ,3 q)family name age hair eyes ------------------------ John 52 brown blue Mary 49 black brown David 18 blonde blue Jessica 13 black blue
The result from
insert is a list of the row indices that have been added to the table:
q)family name| `Jessica age | 13 hair| `black eyes| `blue
The result from
upsert, however, is simply the name of the modified table:
q)`family upsert (`Avery;30;`black;`green) `family q)family name age hair eyes ------------------------ John 52 brown blue Mary 49 black brown David 18 blonde blue Jessica 13 black blue Avery 30 black green
For the unkeyed table
upsert, though returning different results, produced the same outcome –
they both added a row to the family table.
Type out the following code and see what happens when you
upsert into a keyed table.
`keyedtab insert (`p;10;20) `keyedtab insert (`i;11;12) `keyedtab upsert (`i;11;12) `keyedtab upsert (`j;13;20)
The first statement
`keyedtab insert (`p;10;20) inserted a new record under the key
`p, and returned its row: 3.
The second statement
`keyedtab insert (`i;11;12) attempted to insert a new record with the key
`i. But that key already existed. So
insert failed, and returned its own name as an error message.
The third statement
`keyedtab upsert (`i;11;12) found its key
`i already in use and updated the corresponding record, returning the name of the modified table.
The fourth statement
`keyedtab upsert (`j;13;20) inserted a record under the new key
In a keyed table,
insert creates new table records, while
upsert inserts or creates them.
1. Make an unkeyed table with 3 columns: one for the days of the week (as symbols), one for the temperature (integers) and the last with string describing the weather e.g. "cloudy".
2. Key the your table on the day columnn.
3. Add a row to your (keyed) table with a fictional eight day of the week.
4. Replace the entry for Monday with a temperature of 1000 and the description "appallingly wet".