CODEWITHSUNDEEP
kdb+
James
James

Reputation: 1260

KDB/q: How to update a table with row containing disparate columns?

I'm trying to update a table (A) with a secondary table/row(B) as follows:

table:()
row:([]x:`val1;)

table upsert row
table insert row

Which works when all subsequent rows have the same schema. However an edge case I'm trying to account for relates to when the subsequent rows could be of a different schema i.e.:

row:([]x:`val1;y:`val2)

The above throws a mismatch error which would be expected. How do I modify the above insert/upsert logic to account for the advent of updates with a different schema?

Upvotes: 1

Views: 1454

Answers (2)

LeevanT
LeevanT

Reputation: 46

First with the order of operations as stated in your question, without specifing the upsert to be inplace using a ` on the table:

`table upsert row

or assigning it to itself again the second operation would not work and throw out a type error, as its not possible to insert a row into an empty list.

Further to create a table with singleton rows without the operation throwing a rank error, the values of the columns need to be enlisted:

row:([] x:`val1;y:`val2)
'rank
  [6]  row:([] x:`val1;y:`val2)
               ^
row:([] x:enlist `val1;y:enlist `val2)
x    y
---------
val1 val2

To answer your question now.

To be able to upsert or insert rows with different schemas the brute force method as @terrylynch suggested is to use a Union Join (uj) where the table would need to be assigned back onto itself or assigned to a new variable. This operation essentially creates a superset of the two tables. Note the empty list has its type promoted to a table when the first join is done:

row:([] x:enlist `val1;y:enlist `val2)
table:()
row1:([] x:enlist `val1)
table:table uj row1
table:table uj row

table
x    y
---------
val1
val1 val2

It should be noted that once the additional column is uj into the table only data of type symbol can be inserted into that column as the column is now typed. Generally this is a good thing as running queries and operations on typed columns is alot quicker than working with non-typed columns. 

meta table
c| t f a
-| -----
x| s
y| s

If what you are after is being able to dynamically insert into a table data of different types, an empty table can be created that has an untyped column as such:

table:([] a:0N 0N;b:(();())

table
a b
---


meta table
c| t f a
-| -----
a| j
b|

The need for the empty records (sentinal records) of empty lists in column b is to make sure that Q does not type the list to the type of the inserted value.

To insert into this empty schema "inplace":

`table upsert row1
`table

table
a b
------
  ()
  ()
1 `ABB

meta table
c| t f a
-| -----
a| j
b|

Note generally when inserting into the table with a schema specified to be dynamic as above or otherwise instead of inserting a singleton table, the row is specified as a list/mixed list where each element represents the column where the element type matches the column type.

table:([] a:0N 0N;b:(();()))

`table insert (1;`ABB)
,2

`table insert (1;"h")
,3

`table insert (1;3)
,4

table
a b
------
  ()
  ()
1 `ABB
1 "h"
1 3

meta table
c| t f a
-| -----
a| j
b|

Upvotes: 3

terrylynch
terrylynch

Reputation: 13657

You could use a union join

q)table uj row
x    y
---------
val1
val1 val2

Documented here: https://code.kx.com/v2/ref/uj/

Upvotes: 2

Related Questions