New SQL standard features and PostgreSQL

The SQL standard is not standing still. SQL:2016 introduced the latest round of features, and a new standard is expected in 2020. What does that mean for PostgreSQL, the most SQL-conforming relational database system? Let's take a look what new features recent SQL standard releases have brought. One of the main features of SQL:2016 is JSON support. PostgreSQL has been a leader of JSON support among relational databases, and PostgreSQL 12 and beyond will bring the SQL standard interfaces on top of existing JSON support. Other features in SQL:2016 being considered for future PostgreSQL releases include row-pattern recognition and several new functions and data types. A number of new features are being worked on for SQL:2020, among which graph database functionality is the most notable. We'll discuss some of these past and future features, the relationship with other SQL implementations, the SQL standardization process and adoption in PostgreSQL.

Transcript

1. New SQL standard features and PostgreSQL Peter Eisentraut peter.eisentraut@2ndquadrant.com @petereisentraut
2. SQL version history SQL-86: (first version) SQL-89: constraints SQL-92: (first ISO) SQL:1999: regular expressions, recursive queries, triggers, procedures, arrays, structured types, Java stuff, LATERAL ...
3. SQL version history ... SQL:2003: XML, windows functions, sequences, identity columns, statistics aggregates, foreign data SQL:2006: XQuery SQL:2008: INSTEAD OF triggers, TRUNCATE, OFFSET/FETCH ...
4. SQL version history ... SQL:2011: temporal data SQL:2016: row pattern matching, polymorphic table functions, JSON SQL:2020: graph queries, more JSON, streaming(?)
5. PG SQL feature history PG6.4 (1998): sequences PG6.5 (1999): (LIMIT/OFFSET) PG7.0 (2000): TRUNCATE PG7.4 (2003): information schema ...
6. PG SQL feature history ... PG8.0 (2005): savepoints PG8.1 (2005): roles PG8.2 (2006): statistics aggregates PG8.3 (2008): XML PG8.4 (2009): window functions, recursive queries, OFFSET/FETCH ...
7. PG SQL feature history ... PG9.0 (2010): per-column triggers PG9.1 (2011): INSTEAD OF triggers, foreign tables, collations PG9.2 (2012): (json) PG9.3 (2013): LATERAL PG9.4 (2014): (jsonb) PG9.5 (2016): grouping sets, TABLESAMPLE ...
8. PostgreSQL SQL feature history ... PG10 (2017): identity columns, XMLTABLE PG11 (2018): procedures, more window functions PG12 (2019): JSON path, generated columns
9. TODO SQL:2011 temporal data SQL:2016 row pattern recognition SQL:2020 JSON 2.0 SQL:2020 graph query SQL:2020 streaming
10. SQL:2011 temporal data valid time (application time) transaction time (system time)
11. Application time: table definition CREATE TABLE products ( id integer, name text, price decimal(10,2), valid_from timestamptz NOT NULL, valid_till timestamptz NOT NULL, PERIOD FOR valid_at (valid_from, valid_till), PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) );
12. Application time: insert INSERT INTO products VALUES (1, 'widget', 10.00, '2019-01-01', '2010-01-01');
13. Application time: query SELECT * FROM products WHERE name = 'widget'; SELECT * FROM products WHERE name = 'widget' AND valid_from >= '2019-01-01'; SELECT * FROM products WHERE name = 'widget' AND valid_at CONTAINS '2019-07-02';
14. Application time: update id | name | price | valid_from | valid_till ----+--------+-------+---------------------+--------------------- 1 | widget | 10.00 | 2019-01-01 00:00:00 | 2020-01-01 00:00:00 UPDATE products FOR PORTION OF valid_at FROM '2019-08-01' TO '2019-09-01' SET price = 8.50 WHERE id = 1;
15. Application time: update id | name | price | valid_from | valid_till ----+--------+-------+---------------------+--------------------- 1 | widget | 10.00 | 2019-01-01 00:00:00 | 2020-01-01 00:00:00 UPDATE products FOR PORTION OF valid_at FROM '2019-08-01' TO '2019-09-01' SET price = 8.50 WHERE id = 1; id | name | price | valid_from | valid_till ----+--------+-------+---------------------+--------------------- 1 | widget | 10.00 | 2019-01-01 00:00:00 | 2019-08-01 00:00:00 1 | widget | 8.50 | 2019-08-01 00:00:00 | 2019-09-01 00:00:00 1 | widget | 10.00 | 2019-09-01 00:00:00 | 2020-01-01 00:00:00
16. System time: table definition CREATE TABLE products ( id integer, name text, price decimal(10,2), sys_from timestamptz NOT NULL, sys_till timestamptz NOT NULL, PERIOD FOR SYSTEM_TIME (sys_from, sys_till), PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) );
17. System time: query SELECT * FROM products WHERE name = 'widget'; SELECT * FROM products FOR SYSTEM_TIME AS OF '2019-06-15' WHERE name = 'widget'; SELECT * FROM products FOR SYSTEM_TIME FROM '2019-06-01' TO '2019-07-01' WHERE name = 'widget';
18. SQL:2016 row pattern recognition look for regular expressions in sequences of rows
19. RPR: sample data symbol tradeday price XYZ 2009-06-08 50 XYZ 2009-06-09 60 XYZ 2009-06-10 49 XYZ 2009-06-11 40 XYZ 2009-06-12 35 XYZ 2009-06-15 45 XYZ 2009-06-16 45 XYZ 2009-06-17 45 XYZ 2009-06-18 43 XYZ 2009-06-19 47 XYZ 2009-06-22 52 XYZ 2009-06-23 70 XYZ 2009-06-24 60
20. RPR: query SELECT m.symbol, m.matchno, m.startp, m.bottomp, m.endp, m.avgp FROM ticker MATCH_RECOGNIZE ( PARTITION BY symbol ORDER BY tradeday MEASURES MATCH_NUMBER() AS matchno, A.price AS startp, LAST (B.price) AS bottomp, LAST (C.price) AS endp, AVG (U.price) AS avgp ONE ROW PER MATCH AFTER MATCH SKIP PAST LAST ROW PATTERN (A B+ C+) SUBSET U = (A, B, C) DEFINE B AS B.Price < PREV (B.Price), C AS C.Price > PREV (C.Price) ) AS m;
21. RPR: matches symbol tradeday price XYZ 2009-06-08 50 XYZ 2009-06-09 60 A XYZ 2009-06-10 49 B XYZ 2009-06-11 40 B XYZ 2009-06-12 35 B XYZ 2009-06-15 45 C XYZ 2009-06-16 45 XYZ 2009-06-17 45 A XYZ 2009-06-18 43 B XYZ 2009-06-19 47 C XYZ 2009-06-22 52 C XYZ 2009-06-23 70 C XYZ 2009-06-24 60
22. RPR: result symbol matchno startp bottomp endp avgp XYZ 1 60 35 45 45.8 XYZ 2 45 43 70 51.4
23. SQL:2020 JSON 2.0 SQL:2016: JSON functions (JSON_ARRAY, JSON_OBJECT, JSON_TABLE, ...) JSON path using text types SQL:2020 ("JSON 2.0") JSON type
24. SQL:2020 JSON parsing JSON ( '{ ... something ... }' [ {WITH|WITHOUT} UNIQUE KEYS ] )
25. SQL:2020 graph query create a (virtual) property graph from tables query property graph
26. Graph query: example Person name=Alice Account number=3916 ownerOf Account number=6058 transaction amount=$150.00 transaction amount=$300.00 Account number=3224 transaction amount=$500.00 Account number=9794 transaction amount=$450.00 Person name=Bob ownerOf transaction amount=$100.00 Person name=Carol ownerOf Company name=Acme worksFor ownerOf
27. Graph query: tables -- vertex tables CREATE TABLE person (...); CREATE TABLE company (...); CREATE TABLE account (...); -- edge tables CREATE TABLE worksfor (...); CREATE TABLE p_ownerof (...); CREATE TABLE c_ownerof (...); CREATE TABLE transaction (...);
28. Graph query: define graph CREATE PROPERTY GRAPH financial_transactions VERTEX TABLES (person, company, account) EDGE TABLES ( worksfor SOURCE person DESTINATION company, p_ownerof SOURCE person DESTINATION account LABEL ownerof, c_ownerof SOURCE company DESTINATION account LABEL ownerof, transaction SOURCE account DESTINATION account );
29. Graph query: query SELECT owner_name, SUM(amount) AS total_transacted FROM financial_transactions GRAPH_TABLE ( MATCH (p:person WHERE p.name = 'Alice') -[:ownerof]-> (:account) -[t:transaction]- (:account) <-[:ownerof]- (owner:person|company) COLUMNS (owner.name AS owner_name, t_amount AS amount) ) AS ft GROUP BY owner_name;
30. SQL:2020? streaming CREATE STREAM ... ? SELECT STREAM ... ?
31. Conclusion temporal data graph query row pattern recognition (JSON 2.0) (streaming)
32. References PGCon 2019 presentations Paul Jungwirth: Temporal Databases: Theory and Postgres Peter Eisentraut: Graph Databases CB Bohn: PipelineDB
33. SQL standardization ISO/IEC JTC1 SC32 WG3 ANSI INCITS DM32 DM32.2 BSI IST/40 DIN NA 043-01-32 AA ...