Tweet
Tweet

Slide 1

Slide 1 text

New SQL standard features and PostgreSQL Peter Eisentraut [email protected] @petereisentraut

Slide 2

Slide 2 text

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 ...

Slide 3

Slide 3 text

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 ...

Slide 4

Slide 4 text

SQL version history ... SQL:2011: temporal data SQL:2016: row pattern matching, polymorphic table functions, JSON SQL:2020: graph queries, more JSON, streaming(?)

Slide 5

Slide 5 text

PG SQL feature history PG6.4 (1998): sequences PG6.5 (1999): (LIMIT/OFFSET) PG7.0 (2000): TRUNCATE PG7.4 (2003): information schema ...

Slide 6

Slide 6 text

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 ...

Slide 7

Slide 7 text

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 ...

Slide 8

Slide 8 text

PostgreSQL SQL feature history ... PG10 (2017): identity columns, XMLTABLE PG11 (2018): procedures, more window functions PG12 (2019): JSON path, generated columns

Slide 9

Slide 9 text

TODO SQL:2011 temporal data SQL:2016 row pattern recognition SQL:2020 JSON 2.0 SQL:2020 graph query SQL:2020 streaming

Slide 10

Slide 10 text

SQL:2011 temporal data valid time (application time) transaction time (system time)

Slide 11

Slide 11 text

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) );

Slide 12

Slide 12 text

Application time: insert INSERT INTO products VALUES (1, 'widget', 10.00, '2019-01-01', '2010-01-01');

Slide 13

Slide 13 text

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';

Slide 14

Slide 14 text

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;

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

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) );

Slide 17

Slide 17 text

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';

Slide 18

Slide 18 text

SQL:2016 row pattern recognition look for regular expressions in sequences of rows

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

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;

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

RPR: result symbol matchno startp bottomp endp avgp XYZ 1 60 35 45 45.8 XYZ 2 45 43 70 51.4

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

SQL:2020 JSON parsing JSON ( '{ ... something ... }' [ {WITH|WITHOUT} UNIQUE KEYS ] )

Slide 25

Slide 25 text

SQL:2020 graph query create a (virtual) property graph from tables query property graph

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

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 (...);

Slide 28

Slide 28 text

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 );

Slide 29

Slide 29 text

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;

Slide 30

Slide 30 text

SQL:2020? streaming CREATE STREAM ... ? SELECT STREAM ... ?

Slide 31

Slide 31 text

Conclusion temporal data graph query row pattern recognition (JSON 2.0) (streaming)

Slide 32

Slide 32 text

References PGCon 2019 presentations Paul Jungwirth: Temporal Databases: Theory and Postgres Peter Eisentraut: Graph Databases CB Bohn: PipelineDB

Slide 33

Slide 33 text

SQL standardization ISO/IEC JTC1 SC32 WG3 ANSI INCITS DM32 DM32.2 BSI IST/40 DIN NA 043-01-32 AA ...