SQLite 3.44: Interactive release notes

SQlite 3.44 is out, and it has some decent features, so I think now it's a good time to show them off. Let's get started!

ORDER BY in aggregates

Aggregate functions now accept an optional order by after the last parameter. It defines an order in which the function processes its arguments.

order by in aggregates is probably not very useful in avg, count and other functions whose result does not depend on the order of the arguments. However, it can be quite handy for functions like group_concat and json_group_array.

Let's say we have an employees table:

┌────┬───────┬────────┬────────────┬────────┐
│ id │ name  │  city  │ department │ salary │
├────┼───────┼────────┼────────────┼────────┤
│ 11 │ Diane │ London │ hr         │ 70     │
│ 12 │ Bob   │ London │ hr         │ 78     │
│ 21 │ Emma  │ London │ it         │ 84     │
│ 22 │ Grace │ Berlin │ it         │ 90     │
│ 23 │ Henry │ London │ it         │ 104    │
│ 24 │ Irene │ Berlin │ it         │ 104    │
│ 25 │ Frank │ Berlin │ it         │ 120    │
│ 31 │ Cindy │ Berlin │ sales      │ 96     │
│ 32 │ Dave  │ London │ sales      │ 96     │
│ 33 │ Alice │ Berlin │ sales      │ 100    │
└────┴───────┴────────┴────────────┴────────┘

↓ employees.sql

Suppose we want to show employees who work in each of the departments, so we group by department and use group_concat on the name. Previously, we had to rely on the default record order. But now we can concatenate the employees in straight or reverse alphabetical order (or any other order — try changing the order by clause below):

select
  department,
  group_concat(name order by name desc) as names
from employees
group by department;

Now let's group employees by city using json_group_array:

select
  value ->> 'city' as city,
  json_group_array(
    value -> 'name' order by value ->> 'id'
  ) as json_names
from
  json_each(readfile('src/employees.json'))
group by
  value ->> 'city'

Nice!

Better string concatenation

As you probably know, the group_concat function is SQLite (and MySQL) specific. In other databases (namely, PostgreSQL and SQL Server) the function is called string_agg. As of 3.44, SQLite also supports string_agg as an alias for group_concat(expr, sep):

select
  department,
  string_agg(name, ';') as names
from employees
group by department;

Now the only black sheep is Oracle, where the poor function is called listagg.

Speaking of concatenation, this release brings a couple of scalar string functions:

concat(arg, ...) joins non-null arguments without a separator.
concat_ws(sep, arg, ...) joins non-null arguments using the separator.

select concat_ws(': ', id, name) as employee
from employees
where city = 'London';

Both functions are also available in PostgreSQL, MySQL and SQL Server.

Thorough date formatting

The strftime function was already quite powerful. Now it accepts even more formatting verbs:

%F ISO 8601 date: YYYY-MM-DD
%T ISO 8601 time: HH:MM:SS
%R ISO 8601 time: HH:MM
%I hour for 12-hour clock: 01-12
%l hour for 12-hour clock without leading zero: 1-12
%k hour without leading zero: 0-24
%p "AM" or "PM" depending on the hour
%P "am" or "pm" depending on the hour
%e day of month without leading zero: 0-31
%u day of week 1-7 with Monday==1

select "ISO date" as label, strftime('%F', datetime('now')) as value
union all
select "ISO time", strftime('%R', datetime('now')) as value
union all
select "AM/PM time", strftime('%l:%M %P', datetime('now')) as value
union all
select "Day of month", strftime('%e', datetime('now')) as value
union all
select "Day of week", strftime('%u', datetime('now')) as value

ISO formatting is most welcome!

If you are wondering why lowercase p results in uppercase AM/PM, while uppercase P results in lowercase am/pm — it's because the standard C library function strftime does this (with the %p verb).

And a few other things

Integrity checking now works with virtual tables.

Full-text search tables can be used in triggers.

There are some query planner optimizations for partial index scans.

And a handful of other improvements that you can read about in the official release notes.

All in all, a great release!

──

Interactive examples in this post are powered by codapi — an open source tool I'm building. Use it to embed live code snippets into your product docs, online course or blog.

Subscribe to keep up with new posts.