The latest release of SQLite is available with support for Nulls in Order By clauses and the ability to use Filter on aggregate functions.
SQLite is an in-process library that implements a self-contained, serverless, transactional SQL database engine. It supports full text search and has extensions offering JSON support. The entire SQL database with multiple tables, indexes, triggers, and views, is contained in a single disk file. The developers say SQLite is the most used database engine in the world, as it is built into all mobile phones, major web browsers and most operating systems.
The new filter clause lets you add an expression of the form Filter (Where expr) to the aggregate function. This will ensure that only only rows for which the expr is true are included in the aggregate.
The improved handling of Nulls in Order By overcomes the fact that SQLite treats Null values as being smaller than any other values for sorting purposes. Until now this has meant that rows with a Null value in the Order By column would appear at the beginning of an ASC order-by and at the end of a DESC order-by. This can now be changed using either "ASC NULLS LAST" or "DESC NULLS FIRST" syntax.
Index Info and xinfo pragmas have both been improved so they return information about the way tables created using Without RowIS are stored. Pragmas are used to return information about various aspects of data; for example, information about the columns in an index can be read using the index_info pragma like this:
PRAGMA index_info('idx52');
This returns one row for each key column in the named index. If there is no index named index-name but there is a Without RowID table with that name, the pragma now returns the Primary Key columns of the Without RowID table as they are used in the records of the underlying b-tree; in other words, with duplicate columns removed. Other Pragma handling features are now enabled by default.
Elsewhere, the schema parser has been changed so that it throws an error if various elements of the sqlite_master table have been corrupted.
pg_parquet is a new extension by Crunchy Data that allows a PostgreSQL instance to work with Parquet files. With pg_duckdb, pg_analytics and pg_mooncake all of which can access Parquet files, is [ ... ]
The 2024 edition of the State of CSS has been posted, revealing that the latest features of the language not only do away with extra tooling, but even start taking on tasks that previously requir [ ... ]
