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?
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.
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.
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.)
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?
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
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.
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.
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… :)