SQL
Support for SQL is built into the KX Insights Core
A preview of our new SQL capabilities is included in the KX Insights Core.
Using SQL
SQL is invoked by using .s.e
.
Or it can conveniently be entered interactively by prefixing the statement with s)
such as:
q)r:.s.e"select a from t"
q)s)select a from t
The .s.sp
routine may be used for parameter injection from q types:
q)r:.s.sp["select a from t where s in $1 and p>$2"](`AAPL`GOOG;12.34)
Datatype conversions
SQL type KX SQL q name
---------------------------------------------------------------------------
text a 0 list of character vectors
varchar, char(n>1) s 11 symbol
char(1) c 10 char
boolean b 1 boolean
uuid q 2 guid
tinyint x 4 byte
smallint h 5 short
integer i 6 int
bigint j 7 long
real e 8 real
float,double,numeric f 9 float
date d 14 date
time t 19 time
timestamp (w/o tz) p 12 timestamp
varchar q 20 enum
l 20 link (enum used for linked table access)
w null' sql value
SQL language support
Operators
The following operators are supported
+ - * / div
= <> != < > >= <=
is [not] distinct from
a between x and y
not and or
[not]in [not]like is[not]null
SELECT
select [distinct] ns from t (left|right|inner|cross)
join ..join q on f(t)=g(q)
where c
group by 1,g
having h
order by 1,o asc|desc
limit n
Join types include: left
, right
, inner
, and cross
For the join condition on tables t
, q
must be of the form
f(cols of t)=g(cols of q)
Joins may be nested, but natural
, using
and lateral
are not yet implemented.
order by
and group by
expressions may be column names or ordinal numbers of output column, or arbitrary expressions.
limit
with an offset
is not yet implemented.
CTE/WITH SELECT
SQL has support for common table expressions. These queries take the form:
with t1 as (select...), t2 as (select... t1) select... t2
The results are materialized, unless they can be expressed as a union of operations on individual partitions.
Data
The literals
false
null
true
are supported.
Type literals are supported, e.g date'2001-01-01'
String literals may be converted to types date,time,timestamp automatically when possible, e.g.
q)s)select * from t where date in ('2001-01-01','2002-02-02') and time>'12:03'
cast
is also supported to convert from one datatype to another, e.g
cast(x as typename) x::typename
Aggregates
The following aggregates are supported
avg last
count max
count(*) min
first sum
q)t:([] s:`AAPL`GOOG`BARC`VOD; p:13.0 12.1 4.0 5.4; a:1 2 3 4)
q)s)select avg(p), sum(a), min(s) from t
p a s
-------------
8.625 10 AAPL
sum
, avg
, count
, min
, max
are also supported with distinct
.
For example
q)q:([] s:`AAPL`AAPL`GOOG`GOOG; p:4?10.0)
q)s)select count(distinct s) from q
s
-
2
String functions
The following string functions are supported
|| position(x in y)
left position(x,y)
right substring(x from y)
lower substring(x from y for z)
upper substring(x,y,z)
Note, there is no pattern matching on substring
Datetime functions
The following datetime functions are supported
extract
date_trunc
now
q)s)select extract(hour from timestamp '2002-09-17 19:27:45')
extract
-------
19
q)s)select date_trunc('hour', timestamp '2017-03-17 02:09:30');
date_trunc
-----------------------------
2017.03.17D02:00:00.000000000
Math functions
The following math functions are supported
div
floor
round
stddev
trunc
q)t:([] a:1.123 1.456 2.432)
q)s)select round(a) from t
round
-----
1
1
2
Conditional operators
The following conditional operators are supported
case [a] when b then x ... else y
coalesce
nullif
Both the general and ‘simple’ comparison forms of case
are supported.
Subquery
Scalar subquery and correlated subquery are supported.
The following operators support subquery arguments
< [not] in
> [not] exists
=
Combining queries
The following operators are supported for combining queries
union[all]
intersect[all]
except[all]
Other SQL statements
CREATE TABLE t (name type ...)
-
create a regular kdb+ table in memory
INSERT INTO t VALUES (...)
-
insert rows into a table
DROP TABLE t
-
delete the in-memory table
Compliance with ANSI SQL
function | description | status | notes |
---|---|---|---|
E011 | Numeric data types | ||
E011-01 | INTEGER and SMALLINT data types (including all spellings) | Yes | |
E011-02 | REAL, DOUBLE PRECISION, and FLOAT data types | Yes | |
E011-03 | DECIMAL and NUMERIC data types | No | |
E011-04 | Arithmetic operators | Yes | |
E011-05 | Numeric comparison | Yes | |
E011-06 | Implicit casting among the numeric data types | Yes | |
E021 | Character string types | ||
E021-01 | CHARACTER data type (including all its spellings) | Yes | |
E021-02 | CHARACTER VARYING data type (including all its spellings) | Yes | |
E021-03 | Character literals | Yes | |
E021-04 | CHARACTER_LENGTH function | Yes | |
E021-05 | OCTET_LENGTH function | Pending | |
E021-06 | SUBSTRING function | Yes | |
E021-07 | Character concatenation | Yes | |
E021-08 | UPPER and LOWER functions | Yes | |
E021-09 | TRIM function | Pending | |
E021-10 | Implicit casting among the fixed-length and variable-length character string types | Yes | |
E021-11 | POSITION function | Yes | |
E021-12 | Character comparison | Yes | |
E031 | Identifiers | ||
E031-01 | Delimited identifiers | Yes | |
E031-02 | Lower case identifiers | Yes | |
E031-03 | Trailing underscore | Yes | |
E051 | Basic query specification | ||
E051-01 | SELECT DISTINCT | Yes | |
E051-02 | GROUP BY clause | Yes | |
E051-04 | GROUP BY can contain columns Not in |
Yes | |
E051-05 | Select list items can be renamed | Yes | |
E051-06 | HAVING clause | Yes | |
E051-07 | Qualified * in select list | Yes | |
E051-08 | Correlation names in the FROM clause | Yes | |
E051-09 | Rename columns in the FROM clause | No | |
E061 | Basic predicates and search conditions | ||
E061-01 | Comparison predicate | Yes | |
E061-02 | BETWEEN predicate | Yes | |
E061-03 | IN predicate with list of values | Yes | |
E061-04 | LIKE predicate | Partial | Uses q-like syntax, replacing % with *: No underscore |
E061-05 | LIKE predicate: ESCAPE clause | No | |
E061-06 | NULL predicate | Yes | |
E061-07 | Quantified comparison predicate | Pending | |
E061-08 | EXISTS predicate | Yes | |
E061-09 | Subqueries in comparison predicate | Yes | |
E061-11 | Subqueries in IN predicate | Yes | |
E061-12 | Subqueries in quantified comparison predicate | Pending | |
E061-13 | Correlated subqueries | Yes | |
E061-14 | Search condition | Yes | |
E071 | Basic query expressions | ||
E071-01 | UNION DISTINCT table operator | Yes | |
E071-02 | UNION ALL table operator | Yes | |
E071-03 | EXCEPT DISTINCT table operator | Yes | |
E071-05 | Columns combined via table operators need Not have exactly the same data type | Yes | |
E071-06 | Table operators in subqueries | Yes | |
E081 | Basic Privileges | No | |
E081-01 | SELECT privilege at the table level | No | |
E081-02 | DELETE privilege | No | |
E081-03 | INSERT privilege at the table level | No | |
E081-04 | UPDATE privilege at the table level | No | |
E081-05 | UPDATE privilege at the column level | No | |
E081-06 | REFERENCES privilege at the table level | No | |
E081-07 | REFERENCES privilege at the column level | No | |
E081-08 | WITH GRANT OPTION | No | |
E081-09 | USAGE privilege | No | |
E081-10 | EXECUTE privilege | No | |
E091 | Set functions | ||
E091-01 | AVG | Yes | |
E091-02 | COUNT | Yes | |
E091-03 | MAX | Yes | |
E091-04 | MIN | Yes | |
E091-05 | SUM | Yes | |
E091-06 | ALL quantifier | Pending | |
E091-07 | DISTINCT quantifier | Yes | |
E101 | Basic data manipulation | ||
E101-01 | INSERT statement | Yes | |
E101-03 | Searched UPDATE statement | No | |
E101-04 | Searched DELETE statement | No | |
E111 | Single row SELECT statement | Yes | |
E121 | Basic cursor support | No | |
E121-01 | DECLARE CURSOR | No | |
E121-02 | ORDER BY columns need Not be in select list | Yes | |
E121-03 | Value expressions in ORDER BY clause | Yes | |
E121-04 | OPEN statement | No | |
E121-06 | Positioned UPDATE statement | No | |
E121-07 | Positioned DELETE statement | No | |
E121-08 | CLOSE statement | No | |
E121-10 | FETCH statement: implicit NEXT | No | |
E121-17 | WITH HOLD cursors | No | |
E131 | Null value support (nulls in lieu of values) | Partial | See 'nulls' in compatibility Notes |
E141 | Basic integrity constraints | No | |
E141-01 | NoT NULL constraints | No | |
E141-02 | UNIQUE constraints of NoT NULL columns | No | |
E141-03 | PRIMARY KEY constraints | No | |
E141-04 | Basic FOREIGN KEY constraint with the No ACTION default for both referential delete action and referential update action | No | |
E141-06 | CHECK constraints | No | |
E141-07 | Column defaults | No | |
E141-08 | NoT NULL inferred on PRIMARY KEY | No | |
E141-10 | Names in a foreign key can be specified in any order | No | |
E151 | Transaction support | No | |
E151-01 | COMMIT statement | No | |
E151-02 | ROLLBACK statement | No | |
E152 | Basic SET TRANSACTION statement | No | |
E152-01 | SET TRANSACTION statement: ISOLATION LEVEL SERIALIZABLE clause | No | |
E152-02 | SET TRANSACTION statement: READ ONLY and READ WRITE clauses | No | |
E* | Other | ||
E153 | Updatable queries with subqueries | No | |
E161 | SQL comments using leading double minus | Pending | |
E171 | SQLSTATE support | No | |
E182 | Host language binding (previously "Module Language") | Yes | Called from q, can call q |
F021 | Basic information schema | ||
F021-01 | COLUMNS view | Pending | |
F021-02 | TABLES view | Yes | |
F021-03 | VIEWS view | Pending | |
F021-04 | TABLE_CONSTRAINTS view | No | |
F021-05 | REFERENTIAL_CONSTRAINTS view | No | |
F021-06 | CHECK_CONSTRAINTS view | No | |
F031 | Basic schema manipulation | ||
F031-01 | CREATE TABLE statement to create persistent base tables | Partial | No persistence |
F031-02 | CREATE VIEW statement | No | |
F031-03 | GRANT statement | No | |
F031-04 | ALTER TABLE statement: ADD COLUMN clause | No | |
F031-13 | DROP TABLE statement: RESTRICT clause | Partial | No restrict |
F031-16 | DROP VIEW statement: RESTRICT clause | No | |
F031-19 | REVOKE statement: RESTRICT clause | No | |
F041 | Basic joined table | ||
F041-01 | Inner join (but Not necessarily the INNER keyword) | Yes | |
F041-02 | INNER keyword | Yes | |
F041-03 | LEFT OUTER JOIN | Yes | |
F041-04 | RIGHT OUTER JOIN | Yes | |
F041-05 | Outer joins can be nested | Yes | |
F041-07 | The inner table in a left or right outer join can also be used in an inner join | Yes | |
F041-08 | All comparison operators are supported (rather than just =) | No | |
F051 | Basic date and time | ||
F051-01 | DATE data type (including support of DATE literal) | Yes | |
F051-02 | TIME data type (including support of TIME literal) with fractional seconds precision of at least 0 | Yes | |
F051-03 | TIMESTAMP data type (including support of TIMESTAMP literal) with fractional seconds precision of at least 0 and 6 | Yes | |
F051-04 | Comparison predicate on DATE, TIME, and TIMESTAMP data types | Yes | |
F051-05 | Explicit CAST between datetime types and character string types | Yes | |
F051-06 | CURRENT_DATE | Pending | |
F051-07 | LOCALTIME | Pending | |
F051-08 | LOCALTIMESTAMP | Pending | |
F081 | UNION and EXCEPT in views | No | |
F131 | Grouped operations | No | |
F131-01 | WHERE, GROUP BY, and HAVING clauses supported in queries with grouped views | No | |
F131-02 | Multiple tables supported in queries with grouped views | No | |
F131-03 | Set functions supported in queries with grouped views | No | |
F131-04 | Subqueries with GROUP BY and HAVING clauses and grouped views | No | |
F131-05 | Single row SELECT with GROUP BY and HAVING clauses and grouped views | No | |
F* | Other | ||
F181 | Multiple module support | No | |
F201 | CAST function | Yes | |
F221 | Explicit defaults | No | |
F261 | CASE expression | ||
F261-01 | Simple CASE | Yes | |
F261-02 | Searched CASE | Yes | |
F261-03 | NULLIF | Yes | |
F261-04 | COALESCE | Yes | |
F311 | Schema definition statement | No | |
F311-01 | CREATE SCHEMA | No | |
F311-02 | CREATE TABLE for persistent base tables | No | |
F311-03 | CREATE VIEW | No | |
F311-04 | CREATE VIEW: WITH CHECK OPTION | No | |
F311-05 | GRANT statement | No | |
F471 | Scalar subquery values | Yes | |
F481 | Expanded NULL predicate | No | |
F501 | Features and conformance views | No | |
F501-01 | SQL_FEATURES view | No | |
F501-02 | SQL_SIZING view | No | |
F501-03 | SQL_LANGUAGES view | No | |
F812 | Basic flagging | No | |
S011 | Distinct data types | No | |
S011-01 | USER_DEFINED_TYPES view | No | |
T321 | Basic SQL-invoked routines | ||
T321-01 | User-defined functions with No overloading | Yes | q fns can be converted to SQL fns with .s.fs and added to .s.F |
T321-02 | User-defined stored procedures with No overloading | No | |
T321-03 | Function invocation | Yes | |
T321-04 | CALL statement | No | |
T321-05 | RETURN statement | No | |
T321-06 | ROUTINES view | No | |
T321-07 | PARAMETERS view | No | |
T631 | IN predicate with one list element | Yes |