Clash Royale CLAN TAG#URR8PPP

select all columns except two in q kdb historical database

In output I want to select all columns except two columns from a table in q/kdb historical database.
I tried running below query but it does not work on hdb.

delete colid,coltime from table where date=.z.d-1


but it is failing with below error

ERROR: 'par
(trying to update a physically partitioned table)


I referred https://code.kx.com/wiki/Cookbook/ProgrammingIdioms#How_do_I_select_all_the_columns_of_a_table_except_one.3F but no help.

How can we display all columns except for two in kdb historical database?

4 Answers
4

The reason you are getting par error is due to the fact that it is a partitioned table.

par

The error is documented here

trying to update a partitioned table


You cannot directly update, delete anything on a partitioned table ( there is a separate db maintenance script for that)

update

delete

The query you have used as fix is basically selecting the data first in-memory (temporarily) and then deleting the columns, hence it is working.

delete colid,coltime from select from table where date=.z.d-1


You can try the following functional form :

c:cols[t] except `p
?[t;enlist(=;`date;2015.01.01) ;0b;c!c]


Only select queries work on partitioned tables, which you resolved by structuring your query where you first selected the table into memory, then deleted the columns you did not want.

If you have a large number of columns and don't want to create a bulky select query you could use a functional select.

?[table;();0b;x!x((cols table) except `colid`coltime)]


And show all columns except a subset of columns. The column clause expects a dictionary hence I am using the function x!x to convert my list to a dictionary. See more information here

https://code.kx.com/q/ref/funsql/

As nyi mentioned, if you want to permanently delete columns from an historical database you can use the deleteCol function in the dbmaint tools https://github.com/KxSystems/kdb/blob/master/utils/dbmaint.md

deleteCol

Could try a functional select:

?[table;enlist(=;`date;.z.d);0b;x!xcols[table]except`colid`coltime]


Here the last argument is a dictionary of column name to column title, which tells the query what to extract. Instead of deleting the columns you specified this selects all but those two, which is the same query more or less.

To see what the functional form of a query is you can run something like:

parse"select colid,coltime from table where date=.z.d"


And it will output the arguments to the functional select.

You can read more on functional selects at code.kx.com.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.