Behavior of Insert and Upsert in KDB

Question

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?

Torsion

Charlie's response on Google Forums is spot on, for anyone who is interested: groups.google.com/forum/#!topic/personal-kdbplus/JxPLxlmIzJs (response on 10th Aug) – Assess

Answer 1

I believe the problem is with "gg" - it is a list so the insert gets confused whether you're trying to insert one record or multiple. This:

`t insert (`a;"g";4)

works just fine. Unfortunately I do not know other workaround but give insert a list of records of length one:

`t insert (enlist `c;enlist "gg";enlist 4)

I am not sure what's up with upsert, but it might have something to do with its implementation via amend: .[;();,;]

Nasalize

Thanks Alexander. I already know about what you mentioned. I am trying to figure out above mentioned cases. Try following cases to get more view: 1) If your table is not empty then t insert (a; "gg" ;4) works. 2) Switch data type of 'id' and 'id2' column, then following will work: t insert (a;4;"gg"). Also if you look at case b, we don't need to enlist all.Only first primary key col(even if you have more primary keys) and general list col ("gg" in this case). So definitely lot of things are going on behind the scene which is not properly documented on wiki or any other link. – Torsion

One thing I noticed is if a column is declared as a general list it actually receives its type from the first insert, so meta after inserting (a;1;1) will say that the second column is int. I guess that type deduction fails when a list seen on first insert. I often insert a dummy record into tables like that with :: nulls in all generic columns. – Nasalize

Yes, generally column receives its type from first insert in case its a single row insert. But I don't think that is the only thing causing the issue here. Try this: q) t:([s:symbol$()] id:int$();id2:()] / switch id and id2 col datatype q) t insert (a;4;"gg") / works . So in this case it inserts "gg" in a row. – Torsion