Send Feedback
Skip to content

SQL in KDB-X Examples

This page provides practical examples of running SQL in KDB-X. It demonstrates common execution patterns, parameter usage, integration with q, and basic error handling.

SQL interfaces in KDB-X

KDB-X supports several ways to execute SQL, depending on whether you are working interactively, programmatically, or remotely:

  • s) prompt – run SQL interactively from the q console
  • .s.e function – execute SQL strings programmatically from q
  • .s.sp, .s.sq, .s.sx – prepare and execute parameterized SQL
  • PG Wire (pgwire interface) – connect using external SQL clients

The sections below show practical examples of each approach.

Examples

Run SQL interactively s)

The following example uses the s) prompt to enter SQL interactively. Prefix your query with s) to run SQL instead of q.

q)t:([]a: til 3)  // create a table t with a column a
s)SELECT a FROM t

Example

q)trips:([]date:2#.z.D;city:`ldn`ny)
s)SELECT * FROM trips WHERE date=.z.D

Run SQL programmatically .s.e

Use .s.e to run SQL in the KDB-X process command line. Prefix the SQL query with .s.e and wrap it in double quotes.

q).s.init[]        / one-time SQL initialization (per process)
q)query:"SELECT * FROM t"

This example demonstrates how to store the results of one SQL query in a variable and reuse it in another query within KDB-X:

// select two fields and save them in variable x
x:.s.e"SELECT vendor, distance FROM trips WHERE distance<20"

// reuse x in another SQL statement
.s.e"SELECT * FROM x"

Parameterized queries

The following examples demonstrate parameterized SQL queries.

Execute directly with .s.sp

Use .s.sp to inject q type parameters into SQL queries. Use $n notation in the query.

parsedquery:.s.sp[query](parameter list)

This example shows how to execute a parameterized SQL query in KDB-X by injecting q values into placeholders using .s.sp. Here, the query selects rows from table t where s matches either AAPL or GOOG and p is greater than 12.34:

result:.s.sp["SELECT a FROM t WHERE s IN $1 AND p>$2"](`AAPL`GOOG;12.34)

Single-parameter queries

Convert a single parameter into a list because .s.sp always expects a list.

.s.sp["SELECT vendor, fare FROM trips WHERE fare>$1"] enlist 50

Prepare and execute

Use .s.sq to parse and prepare a parameterized query once. Then use .s.sx to execute it multiple times with different parameters.

parsedquery:.s.sq["SELECT a FROM t WHERE s IN $1 AND p>$2"](``;0n);
r1:.s.sx[parsedquery](`AAPL`GOOG;12.34)
r2:.s.sx[parsedquery](`MSFT`VOD;56.78)

Inspect SQL parse tree

Use .s.prx to display the SQL parse tree.

q).s.prx"SELECT * FROM trade WHERE date='2021.11.23' AND symbol IN ('XBTUSD')"

Integrate SQL with q

You can run q functions inside SQL statements or create SQL functions from q functions.

Use qt()

Use qt() to call a q function that returns a table. You can only call qt() in the FROM clause.

s)SELECT a FROM qt(f,x,y...) WHERE ...

This example shows how to query the result of a q function directly from SQL using qt():

s)SELECT a FROM qt('{gettable[`$x;"D"$y;z]}','AAPL','2001.01.01',1)

Use q()

Use q() to call q functions anywhere in a statement and return any q data type.

s)q(t,f,x,y..)

This example demonstrates how to use the q() function within an SQL statement to invoke q code directly and return its result:

s)SELECT q('J','test',10,col) FROM t

Convert a q function into a SQL function

Call .s.F to wrap a q function as a SQL function.

.s.F[`functionname]:.s.fx{code}

This example shows how to expose a q function so it can be called directly from SQL queries in KDB-X:

.s.F[`fun]:.s.fx{x+1}
s)SELECT fun(x) FROM qt('([]1 2 3)')

Error handling

SQL in KDB-X raises errors when you reference columns that do not exist or use unsupported syntax.

s)SELECT non_existing_column FROM trades;
-- Error: column not found

Next steps

  • Learn about supported features in the Reference page.
  • Follow the Quickstart guide for step-by-step setup.