From Kx Wiki
Jump to: navigation, search

The wiki is moving to a new format and this page is no longer maintained. You can find the new page at

The wiki will remain in place until the migration is complete. If you prefer the wiki to the new format, please tell the Librarian why.


Using kdb+

Information.png In this document, the terms 'tuple', 'row', and 'record' are used interchangeably. And similarly for 'column' and 'field'.

How do kdb+ tables differ from SQL relations ?

Relations in SQL are sets. That is, there are no duplicate rows and rows are not ordered. It is possible to define a cursor on a result set and then manipulate the cursor rows in order, but that can't be done in ANSI SQL. kdb+ tables are ordered and may contain duplicates. The order allows a class of very useful aggregates that are unavailable to the relational database programmer without the cumbersome and poorly performing temporal extensions. For instance, there is the concept of the first row and last row of kdb+ table. The functions first and last give open and close prices in financial calculations. For instance, in q you can write

q)first trade


q)last trade

where trade is a kdb+ table.

How do I create a table?

Following the SQL convention, tables are created by naming the items and initializing the items as empty lists. For example, the following expression creates a table with four columns (stock, price, amount and time), and assigns it to trade.

q)trade:([] stock:(); price:(); amount:(); time:())

Each of the table columns is defined to be an empty list, denoted as the parentheses pair () in kdb+.

Information.png In this table, the datatype of the columns is not defined. The first record that is inserted into the table determines the datatype of each column. Subsequents inserts may only insert values of this type, or else a type error is raised.
Information.png This creates a table in memory. You can read here how to store tables on disk.
Information.png Tables can also be created functionally with q primitive functions. Details are available in the Kdb+ Database and Language Primer.

Can I define the type of the columns when I create a table?

Yes. A type descriptor can be specified for each column:

q)trade:([] stock:`symbol$(); price:`float$(); amount:`int$(); time:`time$())

Types are discussed in more detail in the [/trac/browser/kx/kdb+/d/a/q1.htm?format=raw Q language reference manual].

Can I give the column values when creating a table?

Yes, you can give values to initialize the items. By default, item names are taken from corresponding noun names.

q)price:121.3 5.76 8.19
q)amount:1000 500 800
q)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

How do I define a table with a primary key field?

Just write the field definition inside the square brackets. For instance, this is a simple table of financial markets and their addresses (such as the NYSE, the LSE, etc).

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

Can the primary key consist of more than one field?

Yes. For instance, if market names are not unique, the country name can be part of the primary key.

q)market:([name:`symbol$(); country:`symbol$()] address:())
Information.png An alternative to using multiple columns as a primary key is to add to the table a column of unique values (e.g. integers).

Can I specify foreign keys for a table?


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

Imagine that we want to record in our trades table the market (NYSE, LSE, etc) where each trade has been done.

The primary key of the markets table is a foreign key in the trades table.

q)trade:([] stock:`symbol$(); market:`market$(); price:`float$(); amount:`int$(); time:`time$())

How do I insert a record into a table?

q)`trade insert (`ibm; 122.5; 500; 09:04:59:000)

An alternative syntax for insertions is

q)insert [`trade](`ibm; 122.5; 500; 09:04:59:000)


q)insert [`trade; (`ibm; 122.5; 500; 09:04:59:000)]

Can I insert multiple records into a table?

Yes. This is called a bulk insert. The second argument to insert in the previous question is a q list. It can also be a table having the same column names as the first argument. For instance:

q)`trade insert trade

What is an upsert?

q)`trade upsert (`ibm; 122.5; 50; 09:04:59:000)

If the table is not keyed, the above is equivalent to an insert. For a keyed table, it's an update if the key exists in the table and an insert otherwise.

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

q)trade ,: (`ibm; 122.5; 50; 09:04:59:000)
Information.png In the second alternative the table name (trade) is not preceded by a backtick.

upsert can also take a table as an argument.

trade ,: trade

Can a column contain non-scalar values?


q)table:([stock:()] price:())
q)insert[`table; (`intel; enlist (123.2; 120.4; 131.0))]
stock| price
-----| ---------------
intel| 123.2 120.4 131
Information.png Notice the use of enlist.

How do I find out how many rows are in a table?

Use the function count.

q)count trade

How can access the ith row in a table?

This is complex in SQL, but easy in q. The keyword i represents the row index, which can be used in queries.

q)select from trade where i=17
stock price amount time
ibm   122.5 50     09:04:59.000

An alternative is to use indexing.

stock | `ibm
price | 122.5
amount| 500
time  | 09:04:59.000
Information.png The result of the first expression (using select) is a table with a single row, while the result of indexing on a table is a dictionary.
Information.png Indexing cannot be used on a keyed table, as the following example demonstrates.
q)tab: ([stock:()] price:())
q)insert[`tab; (`ibm; 109.5)]
stock| price
-----| -----
ibm  | 109.5

It's also easy to access, say, the second to last row.

q)trade[(count trade) - 2]

Indexing at depth can be used to read a column within a specific row

q)trade[17; `stock]

This is useful for updates too:

q)trade[17; `amount] : 15

When accessing the ith row in a table, what happens if the index is invalid?

If we use select, the result in a table with no rows.

q)select from trade where i = 300000
stock price amount time

If we use indexing, the result is a dictionary containing null values.

q)trade 300000
stock | `
market| `market$`
price | 0n
amount| 0N
time  | 0Nt

How can I access the first/last n rows in a table?

One way is to use the take operator (#). For instance,

stock market price amount time
ibm   nyse   122.5 50     09:04:59.000

gives the first 3 rows, and

stock market price    amount time
intel lse    130.3029 45     09:34:29.000 0

gives the last 3 rows.

Note that the take operator treats a list as circular if the number of elements 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. This takes only as many rows as are available. For instance:

q)count trade
q)count 3 sublist trade
q)count 30 sublist trade

How do I get the rows in a table as a set (no duplicates)?

Use the function distinct.

q)distinct trade

And the following gives the number of distinct rows

q)count distinct trade

What is the syntax of select in kdb+?

select expressions have the following general form.

select [columns] [by columns] from table [where conditions]	

The result of a select expression is a table. For instance:

q)select stock, amount from trade
stock amount
ibm   500

In their simplest form, select expressions extract subtables. However, it is also possible for them to compute new columns or rename existing ones.

q)select stock,newamount:amount+10 from trade where price>100
stock newamount
ibm   510

How do I aggregate column values?


select stock, sum(amount) as total from trade group by stock

In q:

q)select total:sum amount by stock from trade
stock| total
-----| -----
ibm  | 1550
intel| 75
Information.png The column stock is a key in the result table.

How can I add a column with the row number to a table?

The keyword i represents the row index.

q)select rowno:i, stock from trade
rowno stock
0     intel
1     ibm
2     ibm

How do I delete rows from a table?

q)trade: delete from trade where stock=`ibm		
Information.png delete returns a table, but does not modify the trade table in place. The assigment accomplishes that. An alternative that updates the table in place is the following:
q)delete from `trade where stock=`ibm		

How do I update values in a table?


update trade set amount=42+amount where stock='ibm'

In kdb+:

q)trade: update amount:42+amount from trade where stock=`ibm
Information.png update returns a table, but does not modify the underlying table. The assigment accomplishes that.

Or more simply:

q)update amount+42 from `trade where stock=`ibm
Information.png update modifies the table in place like delete deletes in place if a symbol is given as tablename. Also note the default column names, for further information see

How do I replace null values by something else?

Use the fill operator (^). For instance, the following replaces all nulls in column 'amount' by zeroes.

q)trade.amount: 0^trade.amount		

What are parameterized queries?

Select, update and delete expressions can be evaluated in defined functions. For instance:

q)myquery:{[tbl; amt] select stock, time from tbl where amount > amt}
q)myquery[trade; 100]
stock time
ibm   09:04:59.000
Information.png Column names cannot be parameters of a query. Use functional form queries in such cases.

Does kdb+ use stored procedures?

Any user-defined function (of the right type) can be used in a query. For instance:

q)f:{[x] x+42}
q)select stock, f amount from trade
stock amount
ibm   542

Can I write a query using SQL syntax against q tables?

Yes. kdb+ implements a translation layer from SQL to q. The syntax is to prepend s) to the SQL query. For instance:

q)s)select * from trade
Information.png Only a subset of SQL is supported.

How do I write a table to disk?

q)`:filename set trade

Or alternatively

q)save `:trade

You can also specify a directory:

q)`:../filename set trade

How do I read a table from disk?

q)trade: get `:filename

Or alternatively,

q)trade: value `:filename

How do I export a table to a CSV file?

q)save `:trade.csv

How do I import a CSV file into a table?

Assume a file data.csv with columns of type int, string and int.


Then, the following expression does the trick:

q)table: ("ISI"; enlist ",") 0:`data.csv
a   b   c
0   hea 481
10  dfi 579
20  oil 77

What if the CSV file contains data but no column names?

For instance:


We can read the columns like this:

q)Cols: ("ISI";",") 0:`data.csv
0   10  20
hea dfi oil
481 579 77

And we can create the table by first creating a dictionary and flipping it:

q)table: flip `a`b`c!Cols
Information.png Column names must not be the null symbol (`).

How do I export a table to a text file?

q)save `:trade.txt

How do I access a table from an MDB file via ODBC?

From windows, load odbc.k into your kdb+ session, and then load the MDB file.

q w32/odbc.k
q)h: .odbc.load `mydb.mdb

This loads the entire database, which may consist of several tables. Use .odbc.tables to list the tables.

q).odbc.tables  h

Use .odbc.eval to evaluate SQL commands via ODBC.

q).odbc.eval[h;"select * from aa"]

Can I execute kdb+ as a shebang script?

Yes. Since kdb+2.4, kdb+ ignores the first line if it begins with #!. E.g.

$ more ./test.q
#!/usr/bin/env q
$ chmod +x ./test.q
$ ./test.q
KDB+ 3.1 2013.11.20 Copyright (C) 1993-2013 Kx Systems
l64/ ...



Personal tools