« Why doesn’t my query 'select from reallybigtable' ever return? | Main | How does kdb compare to cloud computing? »

How do I store mixed data types in a single column of a table?

The first time we needed a column that can hold values of multiple types, we tried something similar to the following:

q)t: ([] thing: ())
q)meta t
c    | t f a
-----| -----
thing|
q)

True, when you define a table column as an untyped list, it can contain anything. However, the first time you put something in it, the column's type is set to the type of that first inserted item:

q)`t insert `aunt
,0
q)`t insert 33.33
'type
q)meta t
c    | t f a
-----| -----
thing| s

In our example, the type of the thing column has been promoted to symbol; we can't insert numbers into it.

When you want to store elements of different types in the same column, you have to prevent q from performing this type promotion by stuffing a sentinel row (i.e., a row of dummy values), whose value for the column in question is itself a list, into the table:

q)t: ([] thing: enlist "sentinel")
q)meta t
c    | t f a
-----| -----
thing| C

Don't believe the hype. The meta function is trying to help out by actually inspecting the first value in the column when reporting the thing column's type. However, the true type of the column is untyped, i.e., zero:

q)type t `thing
0h
q)

You can, therefore, put whatever you like into t's thing column:

q)`t insert/: (`gave; 42)
1
2
q)t
thing
----------
"sentinel"
`gave
42
q)meta t
c    | t f a
-----| -----
thing| C
q)

Beware cleaning out such a table too thoroughly (in, say, an end-of-day function):

q)delete from `t
`t
q)meta t
c    | t f a
-----| -----
thing|
q)

See this related faq on untyped lists.

PrintView Printer Friendly Version

EmailEmail Article to Friend

Reader Comments

There are no comments for this journal entry. To create a new comment, use the form below.

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Post:
 
All HTML will be escaped. Textile formatting is allowed.