The SQL:2023 standard implements various enhancements and changes
A few days ago the International Organization for Standardization (ISO) approved and published the international standard SQL:2023 (ISO/IEC 9075), which is defined as the ninth edition of the specification for the SQL language used to manipulate data in relational DBMSs. The last update to the specification was published in 2016 (SQL:2016).
For those who are unaware of SQL (Structured Query Language) you should know that it is a standardized language for managing relational databases and performing various operations on the data they contain. Created in the 1970s, SQL is regularly used not only by database administrators, but also by developers writing data integration scripts, and by data analysts looking to set up and run analytical queries. The standard is regularly updated to add new features and make changes to the existing language.
What's new in the new SQL:2023 specification?
Added SQL/PGQ extension (Property Graph Queries) to manipulate interconnected data sets that form a graph. Basically, this new function makes it easy to query data in tables as if it were in a graph database, providing an alternative, perhaps more intuitive, to writing complex join queries. This feature could be very welcome in the data science community.
Another change that stands out is that defined the ability to customize the behavior of NULL value processing in the presence of the "UNIQUE" constraint. If "UNIQUE NULLS DISTINCT" is specified, NULL values added to the database will be treated as unique.
In addition to that, we can also find that The possibilities of performing the "ORDER BY" operation have been expanded in clustered tables. The specification now allows operations to sort tables grouped by a column that does not appear in the SELECT output list of the clustered table. Previously, most DBMSs allowed such manipulations, but the specification did not define such a possibility.
We can also find that I know added multi-character variants of the TRIM function: LTRIM, RTRIM, and BTRIM, which allow characters specified in the list to be cut from the beginning or end of the string. Compared to TRIM, the new functions have a simpler syntax.
Se extended the ability to detect cycles in recursive queries using the expression "CYCLE«. A field with a loop marker can now be of type "boolean" instead of a string, and pass the loop sign as true and false values.
Of the other changes that stand out:
- Added new LPAD and RPAD functions to pad a string to a given size. For example:
- For the types "VARCHAR" and "CHARACTER VARYING" it is allowed not to specify the maximum size, in this case the maximum size will depend on the DBMS implementation.
- Added a new aggregate function any_value(), which returns an arbitrary non-null value from the input dataset.
- Added the ability to specify hexadecimal, binary, and octal literals. For example:
- The use of the underscore character in the number is allowed to increase the visibility of the digital literals.
- Significantly expanded capabilities related to processing data in JSON format.
- Added a separate JSON type (in the SQL:2016 standard, JSON data was required to be stored in fields with string types).
- Implemented support for JSON_SERIALIZE, JSON_SCALAR, and IS JSON operations.
- Added 14 new methods to apply to SQL/JSON values within the SQL/JSON language.
Finally, If you are interested in knowing more about it, You can consult the details as well as practical examples of the changes implemented In the following link.
It is worth mentioning that in PostgreSQL, most of the functions proposed in SQL:2023 are already available o are planned for inclusion in the next major release, while for the ANY_VALUE support part, underscores in numbers, hex/binary/octal literals, and hex literals in SQL/JSON will appear in the PostgreSQL fall release.
Enhanced feature support expected for JSON type, SQL/JSON syntax simplified, new JSON methods, and the PGQ extension in versions after PostreSQL 16, but work in these areas has not yet begun. The rest of the SQL:2023 functions are already available in existing versions of PostreSQL.