KDB+/q: Creating a Null Table with Data Types
In this post, we’ll explore how to create a null table in KDB+ using the data types from an existing table. This is useful when you want to initialize a table structure with the correct schema but no data.
Let’s walk through the process using a sample student table.
Step 1: Start with the Existing Table
Assume we have a table named student:
q)student
name roll_no class age joined_date
-----------------------------------
Sam 1 A 14 2025.06.01
Ram 2 B 14 2025.06.02
Tom 3 B 14 2025.06.02
Jerry 4 A 14 2025.06.01
Sai 5 B 14 2025.06.03 Step 2: Get the Metadata
We use the meta keyword to retrieve the metadata of the student table and store it in a variable table1:
q)table1:meta student
q)table1
c | t f a
-----------| -----
name | s
roll_no | i
class | c
age | i
joined_date| d Step 3: Create a Dictionary for Data Types
Now, let’s define a mapping from type letters to their corresponding data types as strings. We’ll store this in a dictionary named datatypeDict.
q)datatypeDict:(("s";"i";"c";"d")!("`symbol$";"`int$";"`char$";"`date$"))
q)datatypeDict
s| "`symbol$"
i| "`int$"
c| "`char$"
d| "`date$"Step 4: Add a symDatatype Column
Get Sathish Vadivel’s stories in your inbox
Join Medium for free to get updates from this writer.
Using the dictionary, we update table1 with a new column called symDatatype, which holds the type conversion in string format. We save the result as table2.
q)table2:update symDatatype:datatypeDict[t] from meta student
q)table2
c | t f a symDatatype
-----------| -----------------
name | s "`symbol$"
roll_no | i "`int$"
class | c "`char$"
age | i "`int$"
joined_date| d "`date$" Step 5: Construct the Final Column Type Format
We now create another column colType, which combines the data type and column name in the format (datatype$columnname). This is saved as table3.
q)table3:update colType:{x,string y}'[symDatatype;c] from table2
q)table3
c | t f a symDatatype colType
-----------| -------------------------------------
name | s "`symbol$" "`symbol$name"
roll_no | i "`int$" "`int$roll_no"
class | c "`char$" "`char$class"
age | i "`int$" "`int$age"
joined_date| d "`date$" "`date$joined_date"Step 6: Create the Null Table Function
Let’s define a function nullTableFunction that takes a table name (as a symbol) and constructs a null table with the same schema as the original.
q)nullTableFunction:{value ((string x),":([] ",(ssr[(" " sv (exec colType from table3));" ";":(); "]),":())")}Step 7: Execute the Function
Run the function with the table name. This will recreate the structure of the student table with no data.
q)nullTableFunction [`student]Now, check the contents and metadata of the table:
q)student
name roll_no class age joined_date
----------------------------------
q)meta student
c | t f a
-----------| -----
name | s
roll_no | i
class | c
age | i
joined_date| d The student table is successfully recreated with the same columns and data types, but with no records — just like we intended!
Mission accomplished 🚀
