Behaviour of Insert and Upsert in KDB

[RAHUL ASATI]

I have following table:

           q) t:([s:`symbol$()] id:();id2:`int$())

where 's' is a primary key and 'id' col has general type. I am trying to understand following behavior when inserting a list (string in this ex.) in 'id' column:

a) Upsert works but Insert fails:

          q) `t insert (`a;"gg";4)     // 'type 
          q) `t upsert (`a;"gg";4)     // works
 

b) Insert requires primary key to be enlisted as well:

    q)`t insert  (`a;enlist "gg";4)    // 'length
    q)`t insert  (enlist `a;enlist "gg";4)  // works
 

What's going on behind the scene?

[Flying]

This behaviour is caused by q's treatment of generic lists. The fact that id is untyped causes the confusion to q in both cases. Note: "gg" is a 2-element list whereas `a and 4 are both atoms.

[RAHUL ASATI]

That I understand, my doubt is:

case a) Why Upsert works in this case but not Insert.

case b) Why enlist on primary key is required.

[Charles Skelton]

some rules for insert/upsert are documented here
http://kx.com/q/d/q.htm

section 6

(recall that a keyed table is a dict where both key and value are themselves tables.)

[RAHUL ASATI]

Thanks Charles.  I've already read those rules but it is still not clear to me. Here are some more  examples regarding those cases which increases the confusion:

For case a) : If we switch the datatype of value table columns like:

      q) t:([s:`symbol$()] id:`int$();id2:())

 Now insert works
    q) `t insert (`a;4;"gg")   / works

why?

For case b): Lets make two cols as Primary key:

   q) t:([p1:`symbol$();p2:`symbol$()] id:();id2:`int$())

In this case also, only first primary column needs an enlist and not both (or all primary cols in general case).

  q) `t insert ( enlist   `a; `b;enlist   "gg";4)  / works
  q) `t insert ( enlist   `a; enlist `b;enlist   "gg";4)   / works
 
  Could you please give some more view on it?

[Charles Skelton]

upsert and insert expect lists differently:
q)t:([s:`symbol$()] id:();id2:`int$())
q)`t insert flip enlist(`a;"gg";4);  / insert expects columns
q)`t upsert (`b;"gg";4); / upsert expects rows
q)t
s| id   id2
-| --------
a| "gg" 4 
b| "gg" 4 
q)`t upsert((`c;"ab";4);(`d;"cd";4)); / now add 2 rows
q)`t insert(`e`f;("ef";"gh");4 4);
q)t
s| id   id2
-| --------
a| "gg" 4 
b| "gg" 4 
c| "ab" 4 
d| "cd" 4 
e| "ef" 4 
f| "gh" 4 

The usual case would be inserting/upserting into a non-empty column. e.g.

q)t:([s:`symbol$()] id:();id2:`int$())
q)`t upsert (`a;"bc";0);
q)`t insert (`b;"de";1);   / insert now has more info about interpreting the record based on the target
q)t
s| id   id2
-| --------
a| "bc" 0 
b| "de" 1 

As Freddie said, for the very first insert to an empty table with a column of type 0, it complicates trying to figure out the type to insert when at least one element of your record is a vector.

Btw, sometimes users define a typeless schema and rely on automatic retyping of the column after the first insert, e.g.

q)t:([]();());`t insert (0;"ab");t / single row
x x1 
------
0 "ab"

q)t:([]();());`t insert ("ab";0);t / 2 rows, scalar extends 0
x x1
----
a 0
b 0

q)t:([]();());`t upsert (0;"ab");t / list of a single row
x x1 
------
0 "ab"

q)t:([]();());`t upsert ("ab";0);t / 2 rows, scalar extends 0
x   x1
-------
"a" "b"
0   0 

and you can see the result depends upon the first element of the record - figuring out whether this is a list of one record or a list of multiple records.

You can remove ambiguity by naming the columns
q)t:([]();());`t upsert `x`x1!("ab";0);t
x    x1
-------
"ab" 0

Another way to view this problem, if you define the type of the empty list through the use of on-disk empties (types 77 thru 97), then the ambiguity is also eliminated between these scenarios (including insert/upsert)

q).Q.Xf[`char;`:char];
q)t:([]get`:char;());`t insert ("ab";0);t
x    x1
-------
"ab" 0

q)t:([]();get`:char);`t insert (0;"ab");t
x x1 
------
0 "ab"

hth,

Charlie

[Flying]

Thanks, Charles. The .Q.Xf tip was cool! Since it's not documented in the wiki, I now know how to 'create' such nested types when needed.

[RAHUL ASATI]

Thanks Charles for the detailed explanation and for some new concepts. So basically I can say that insert behavior is not consistent and  changes according  to input cases and table  structure and that I think is little dangerous.

[Manish Patel]

no more dangerous than any other overloaded function in q, such as ? - the rules are to be understood. Charles' explanation is good, should should go into the wiki... :)

--
You received this message because you are subscribed to the Google Groups "Kdb+ Personal Developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to personal-kdbpl...@googlegroups.com.
To post to this group, send email to personal...@googlegroups.com.
Visit this group at http://groups.google.com/group/personal-kdbplus.
For more options, visit https://groups.google.com/d/optout.