CODEWITHSUNDEEP
kdb+
pom
pom

Reputation: 340

KDB: why am I getting a type error when upserting?

I specified the columns to be of type String. Why am I getting the following error: 

q)test: ([key1:"s"$()] col1:"s"$();col2:"s"$();col3:"s"$())
q)`test upsert(`key1`col1`col2`col3)!(string "999"; string "693"; string "943"; 
string "249")

'type
[0]  `test upsert(`key1`col1`col2`col3)!(string "999"; string "693"; string "9
43"; string "249")

Upvotes: 1

Views: 2793

Answers (2)

Eliot Robinson
Eliot Robinson

Reputation: 666

To do exactly this, you can remove the types of the list you defined in test:

q)test: ([key1:()] col1:();col2:();col3:())
q)test upsert (`key1`col1`col2`col3)!("999";"693";"943";"249")
key1 | col1  col2  col3
-----| -----------------
"999"| "693" "943" "249"

The reason you are getting a type error is because "s" corresponds to a list of symbols, not a list of characters. you can check this by using .Q.ty: 

q).Q.ty `symbol$()
"s"
q).Q.ty `char$()
"c"

It is (generally) not a great idea to set the keys as nested list of chars, you might find it better to set them as integers ("i") or longs ("j") as in:

test: ([key1:"j"$()] col1:"j"$();col2:"j"$();col3:"j"$())

Having the keys as integers/longs will make the upsert function behave nicely. Also note that a table is a list of dictionaries, so each dictionary can be upserted inidividually as well as a table being upserted:

q)`test upsert (`key1`col1`col2`col3)!(9;4;6;2)
`test
q)test
key1| col1 col2 col3
----| --------------
9   | 4    6    2
q)`test upsert (`key1`col1`col2`col3)!(8;6;2;3)
`test
q)test
key1| col1 col2 col3
----| --------------
9   | 4    6    2
8   | 6    2    3
q)`test upsert (`key1`col1`col2`col3)!(9;1;7;4)
`test
q)test
key1| col1 col2 col3
----| --------------
9   | 1    7    4
8   | 6    2    3
q)`test upsert ([key1: 8 7] col1:2 4; col2:9 3; col3:1 9)
`test
q)test
key1| col1 col2 col3
----| --------------
9   | 1    7    4
8   | 2    9    1
7   | 4    3    9

Upvotes: 1

Sean O'Hagan
Sean O'Hagan

Reputation: 1697

You have a few issues:

  • an array of chars in quotes is a string so no need to write string "abc"
  • string "aaa" will split the string out in strings of strings
  • your initial defined types are symbols "s" and not strings

This will allow you to insert as symbols:

q)test: ([key1:"s"$()] col1:"s"$();col2:"s"$();col3:"s"$())
q)`test upsert(`key1`col1`col2`col3)!`$("999"; "693"; "943"; "249")
`test

This will keep them as strings:

q)test: ([key1:()] col1:();col2:();col3:())
q)`test upsert(`key1`col1`col2`col3)!("999"; "693"; "943"; "249")
`test

Have a look at the diffs in metas of the two

HTH, Sean

Upvotes: 1

Related Questions