SQL Avançado com PostgreSQL

- Subconsultas no SELECT, INSERT, UPDATE e WHERE
- INSERT ON CONFLICT (o MERGE do PostgreSQL)
- DML com RETURNING
- Full Text Search e as maravilhas dos índices GIN
- Foreign Data Wraper (acesso a dados externos como todo SGDB deveria fazer)
- Window function

Transcript

1. SQL Avançado com PostgreSQL
2. AGENDA SQL Avançado com PostgreSQL Subconsultas INSERT ON CONFLICT DML c/ RETURNING Full Text Search (FTS) Foreign Data Wrapper Window Function
3. SELECT (...) FROM (...) WHERE column_name IN (...) AND/OR column_name NOT IN (...) AND/OR EXISTS (...) AND/OR NOT EXISTS (...) https://www.postgresql.org/docs/current/sql-select.html Subconsultas: SELECT
4. SELECT t1.a, t1.b, t1.c FROM t1 WHERE t1.a IN (SELECT a FROM t2 WHERE b > 5); SELECT t1.a, t1.b, t1.c FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE b > 5 AND t1.a = t2.a); Subconsultas: SELECT
5. [ WITH [ RECURSIVE ] with_query [, ...] ] INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ] [ OVERRIDING { SYSTEM | USER } VALUE ] { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query } [ ON CONFLICT [ conflict_target ] conflict_action ] [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ] https://www.postgresql.org/docs/current/sql-insert.html Subconsultas: INSERT
6. INSERT INTO table_name query ; INSERT INTO t1 SELECT a, b, c FROM t2 WHERE a < 50; CREATE TABLE t1 AS SELECT a, b, c FROM t2 WHERE a < 50; Subconsultas: INSERT
7. [ WITH [ RECURSIVE ] with_query [, ...] ] DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ] [ USING from_item [, ...] ] [ WHERE condition | WHERE CURRENT OF cursor_name ] [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ] https://www.postgresql.org/docs/current/sql-delete.html Subconsultas: DELETE
8. DELETE FROM table_name USING from_item WHERE condition DELETE FROM t1 WHERE a IN (SELECT a FROM t2 WHERE b < 50); DELETE FROM t1 USING t2 WHERE t1.a = t2.a AND t2.b < 50; Subconsultas: DELETE
9. [ WITH [ RECURSIVE ] with_query [, ...] ] UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ] SET { column_name = { expression | DEFAULT } | ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) | ( column_name [, ...] ) = ( sub-SELECT ) } [, ...] [ FROM from_item [, ...] ] [ WHERE condition | WHERE CURRENT OF cursor_name ] [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ] https://www.phttps://www.postgresql.org/docs/current/sql-update.html Subconsultas: UPDATE
10. UPDATE table_name SET column_name = expression FROM from_item WHERE condition UPDATE t1 SET a = (SELECT a FROM t2 WHERE t1.id = t2.id AND t2.b < 50) WHERE id = 37; UPDATE t1 SET t1.a = t2.a FROM t2 WHERE t1.id = t2.id AND t2.b < 50 AND t1.id = 37; Subconsultas: UPDATE
11. UPDATE t1 SET (a, b) = (SELECT a, b FROM t2 WHERE t1.id = t2.id AND t2.b < 50) WHERE id = 37; UPDATE t1 SET t1.a = t2.a, t1.b = t2.b FROM t2 WHERE t1.id = t2.id AND t2.b < 50 AND t1.id = 37; Subconsultas: UPDATE
12. [ WITH [ RECURSIVE ] with_query [, ...] ] INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ] [ OVERRIDING { SYSTEM | USER } VALUE ] { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query } [ ON CONFLICT [ conflict_target ] conflict_action ] [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ] where conflict_target can be one of: ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ] ON CONSTRAINT constraint_name and conflict_action is one of: DO NOTHING DO UPDATE SET { column_name = { expression | DEFAULT } | ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) | ( column_name [, ...] ) = ( sub-SELECT ) } [, ...] [ WHERE condition ] INSERT ON CONFLICT
13. AGENDA SQL Avançado com PostgreSQL Subconsultas INSERT ON CONFLICT DML c/ RETURNING Full Text Search (FTS) Foreign Data Wrapper Window Function
14. INSERT INTO table_name [ ( column_name [, ...] ) ] VALUES ( expression [, ...] ) [ ON CONFLICT [ conflict_target ] conflict_action ] where conflict_target can be one of: ( index_column_name | index_expression) | ON CONSTRAINT constraint_name and conflict_action is one of: DO NOTHING DO UPDATE SET index_expression = expression INSERT ON CONFLICT
15. INSERT INTO t1 (a, b) VALUES ( 10, 20) ON CONFLICT (a) DO NOTHING; INSERT INTO t1 (a, b) VALUES ( 10, 20) ON CONFLICT (a) DO UPDATE SET b = EXCLUDED.b; INSERT ON CONFLICT
16. [ WITH [ RECURSIVE ] with_query [, ...] ] INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ] [ OVERRIDING { SYSTEM | USER } VALUE ] { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query } [ ON CONFLICT [ conflict_target ] conflict_action ] [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ] https://www.postgresql.org/docs/current/sql-insert.html INSERT com RETURNING
17. AGENDA SQL Avançado com PostgreSQL Subconsultas INSERT ON CONFLICT DML c/ RETURNING Full Text Search (FTS) Foreign Data Wrapper Window Function
18. INSERT INTO table_name [ ( column_name [, ...] ) ] VALUES ( expression [, ...] ) RETURNING * | output_expression INSERT INTO t1 (b) VALUES (10) RETURNING *; INSERT INTO t1 (b, c, d) VALUES (10, 20, 30) RETURNING a, b; INSERT com RETURNING
19. [ WITH [ RECURSIVE ] with_query [, ...] ] UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ] SET { column_name = { expression | DEFAULT } | ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) | ( column_name [, ...] ) = ( sub-SELECT ) } [, ...] [ FROM from_item [, ...] ] [ WHERE condition | WHERE CURRENT OF cursor_name ] [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ] UPDATE com RETURNING
20. UPDATE table_name SET column_name = expression WHERE condition RETURNING * | output_expression UPDATE t1 SET a = a + 10 WHERE b < 50 RETURNING *; UPDATE t1 SET a = a + 10 WHERE b < 50 RETURNING id, a; UPDATE com RETURNING
21. [ WITH [ RECURSIVE ] with_query [, ...] ] DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ] [ USING from_item [, ...] ] [ WHERE condition | WHERE CURRENT OF cursor_name ] [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ] DELETE com RETURNING
22. DELETE FROM table_name WHERE condition RETURNING * | output_expression DELETE FROM t1 WHERE a < 50 RETURNING *; DELETE FROM t1 WHERE a < 50 RETURNING a, b; DELETE com RETURNING
23. AGENDA SQL Avançado com PostgreSQL Subconsultas INSERT ON CONFLICT DML c/ RETURNING Full Text Search (FTS) Foreign Data Wrapper Window Function
24. • Busca textual nativa • Utiliza dicionário em diversas línguas • Busca variações da mesma palavra através do dicionário • Organiza palavras relevantes em tokens através da função to_tsvector • Realiza buscas nos tokens através da função to_tsquery • Classifica a qualidade da busca através da função ts_rank • Utiliza o operador @@ para comparar o resultado do to_tsvactor com outras expressões • Utiliza índices específicos: GIST e GIN https://www.postgresql.org/docs/current/textsearch.html https://www.postgresql.org/docs/current/functions-textsearch.html Full Text Search (FTS)
25. • Busca textual nativa • Utiliza dicionário em diversas línguas • Busca variações da mesma palavra através do dicionário • Organiza palavras relevantes em tokens através da função to_tsvector • Realiza buscas nos tokens através da função to_tsquery • Classifica a qualidade da busca através da função ts_rank • Utiliza o operador @@ para comparar o resultado do to_tsvactor com outras expressões • Utiliza índices específicos: GIST e GIN https://www.postgresql.org/docs/current/textsearch.html https://www.postgresql.org/docs/current/functions-textsearch.html Full Text Search (FTS)
26. to_tsvector: postgres=# SELECT 'a fat cat sat on a mat and ate a fat rat' ::tsvector; tsvector ---------------------------------------------------- 'a' 'and' 'ate' 'cat' 'fat' 'mat' 'on' 'rat' 'sat' postgres=# SELECT to_tsvector('a fat cat sat on a mat and ate a fat rat' ); to_tsvector ----------------------------------------------------- 'ate':9 'cat':3 'fat':2,11 'mat':7 'rat':12 'sat':4 Full Text Search (FTS)
27. to_tsvector: postgres=# SELECT * FROM unnest(to_tsvector('fat cats ate fat rats')); lexeme | positions | weights --------+-----------+--------- ate | {3} | {D} cat | {2} | {D} fat | {1,4} | {D,D} rat | {5} | {D} Full Text Search (FTS)
28. to_tsquery: postgres=# SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector @@ 'cat & rat'::tsquery; ?column? ---------- t (1 row) postgres=# SELECT to_tsvector('fat cats ate fat rats') @@ to_tsquery('fat & rat'); ?column? ---------- t Full Text Search (FTS)
29. • Dicionários atual: postgres=# \dF List of text search configurations Schema | Name | Description ------------+------------+--------------------------------------- pg_catalog | danish | configuration for danish language pg_catalog | dutch | configuration for dutch language pg_catalog | english | configuration for english language pg_catalog | finnish | configuration for finnish language pg_catalog | french | configuration for french language pg_catalog | german | configuration for german language pg_catalog | hungarian | configuration for hungarian language pg_catalog | italian | configuration for italian language pg_catalog | norwegian | configuration for norwegian language pg_catalog | portuguese | configuration for portuguese language pg_catalog | romanian | configuration for romanian language pg_catalog | russian | configuration for russian language pg_catalog | simple | simple configuration pg_catalog | spanish | configuration for spanish language pg_catalog | swedish | configuration for swedish language pg_catalog | turkish | configuration for turkish language Full Text Search (FTS)
30. Dicionário padrão: postgres=# show default_text_search_config; default_text_search_config ---------------------------- pg_catalog.english postgres=# SELECT to_tsvector('O rato roeu a roupa do rei de Roma'); to_tsvector ----------------------------------------------------------- 'de':8 'o':1 'rato':2 'rei':7 'roeu':3 'roma':9 'roupa':5 postgres=# SELECT to_tsvector( 'portuguese','O rato roeu a roupa do rei de Roma'); to_tsvector ------------------------------------------- 'rat':2 'rei':7 'roeu':3 'rom':9 'roup':5 Full Text Search (FTS)
31. • to_tsquery([ config regconfig, ] querytext text) • Operadores com to_tsvector ◦ to_tsvector @@text ◦ to_tsvector @@ to_tsquery • Operadores em querytext: ◦ & (e) ◦ | (ou) ◦ ! (não) ◦ <-> (seguido por) • Full Text Search (FTS)
32. CREATE TABLE post ( id integer PRIMARY KEY, title varchar(200) NOT NULL, subject varchar(200) NOT NULL, body varchar NOT NULL ; CREATE INDEX ON post USING GIN (to_tsvector('portuguese', body)); SELECT title FROM post WHERE to_tsvector(body) @@ to_tsquery('create & table'); CREATE INDEX ON post USING GIN (to_tsvector('portuguese', title || ' ' || subject || ' ' || body)); SELECT title FROM post WHERE to_tsvector(title || ' ' || subject || ' ' || body) @@ to_tsquery('create & table'); Full Text Search (FTS)
33. ALTER TABLE post ADD COLUMN search_col tsvector GENERATED ALWAYS AS (to_tsvector('portuguese', coalesce(title,'') || ' ' || coalesce(subject,'') || ' ' || coalesce(body,''))) STORED; CREATE INDEX ON post USING GIN (search_col); SELECT title FROM post WHERE search_col @@ to_tsquery('create & table'); Full Text Search (FTS)
34. AGENDA SQL Avançado com PostgreSQL Subconsultas INSERT ON CONFLICT DML c/ RETURNING Full Text Search (FTS) Foreign Data Wrapper Window Function
35. • Permite ler e gravar dados a partir de fontes externas de dados • Utilizam extensões específicas para cada fonte • Após feito o mapeamento, as fontes externas se comportam como tabelas locais • Possui uma sequência de DDLs específica para fazer o mapeamento: ◦ CREATE EXTENSION ◦ CREATE SERVER ◦ CREATE USER MAPPING ◦ CREATE FOREIGN TABLE ◦ IMPORT FOREIGN SCHEMA https://wiki.postgresql.org/wiki/Foreign_data_wrappers https://www.postgresql.org/docs/current/postgres-fdw.html https://www.postgresql.org/docs/16/sql-createserver.html https://www.postgresql.org/docs/16/sql-createusermapping.html https://www.postgresql.org/docs/16/sql-createforeigntable.html https://www.postgresql.org/docs/16/sql-importforeignschema.html Foreign Data Wrapper (FDW)
36. CREATE EXTENSION postgres_fdw; CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '192.83.123.89', port '5432', dbname 'foreign_db'); CREATE USER MAPPING FOR local_user SERVER foreign_server OPTIONS (user 'foreign_user', password 'password'); CREATE FOREIGN TABLE foreign_table ( id integer NOT NULL, data text ) SERVER foreign_server OPTIONS (schema_name 'some_schema', table_name 'some_table'); IMPORT FOREIGN SCHEMA foreign_schema FROM SERVER foreign_server INTO local_schema; Foreign Data Wrapper (FDW)
37. AGENDA SQL Avançado com PostgreSQL Subconsultas INSERT ON CONFLICT DML c/ RETURNING Full Text Search (FTS) Foreign Data Wrapper Window Function
38. • Funções de agregação que se aplicam a um conjunto de registros definidos por uma janela • Ideal para processar dados de uma linha que dependem de valores de linhas adjacentes • Simplificam brutalmente o o trabalho em trabalhos de estatísticas com desempenho muito superior https://www.postgresql.org/docs/current/tutorial-window.html https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS https://www.postgresql.org/docs/current/functions-window.html Window Functions
39. • Criação de uma “Curva ABC” c/ Window Functions • Exemplos aqui utilizam a base “pagila”: https://ftp.postgresql.org/pub/projects/pgFoundry/dbsamples/pagila/pagila/pagila-0.10.1.zip • A tabela sales_title, foi criada com o seguinte comando SQL: CREATE TABLE sales_title AS SELECT f.title, c.name AS category, sum(p.amount) AS sales FROM (((((payment p JOIN rental r ON ((p.rental_id = r.rental_id))) JOIN inventory i ON ((r.inventory_id = i.inventory_id))) JOIN film f ON ((i.film_id = f.film_id))) JOIN film_category fc ON ((f.film_id = fc.film_id))) JOIN category c ON ((fc.category_id = c.category_id))) GROUP BY c.name, f.title; Window Functions
40. SELECT sum(sales) AS total_sales FROM sales_title; SELECT sum(sales) OVER () AS total_sales FROM sales_title; Window Functions
41. SELECT category, sum(sales) AS sales_category FROM sales_title GROUP BY category; SELECT category, sum(sales) OVER (partition by category) FROM sales_title; Window Functions
42. SELECT title, category, sum(sales) OVER (partition by category) AS sales_category, sum(sales) OVER () AS sales_total FROM sales_title; Window Functions
43. SELECT title, category, sales, rank() OVER (ORDER BY sales DESC) AS rank FROM sales_title; Window Functions
44. SELECT title, category, sum(sales) OVER (PARTITION BY category ORDER BY sales DESC), sum(sales) OVER (PARTITION BY category), sum(sales) OVER () AS sales_total FROM sales_title; Window Functions
45. SELECT title, category, sales, rank() OVER (PARTITION BY category ORDER BY sales DESC) AS rank FROM sales_title; Window Functions
46. SELECT title, category, sales, rank() OVER (PARTITION BY category ORDER BY sales DESC) AS rank, sum(sales) OVER (PARTITION BY category ORDER BY sales DESC) AS sales_category FROM sales_title; Window Functions
47. SELECT category, rank() OVER (PARTITION BY category ORDER BY sales DESC) AS rank, title, sales, TRUNC(( sum(sales) OVER (PARTITION BY category ORDER BY sales DESC) / sum(sales) OVER (PARTITION BY category))*100,1) AS porcentagem FROM sales_title; Window Functions
48. SELECT category, rank, title, porcentagem, CASE WHEN porcentagem <= 20 THEN 'A' WHEN porcentagem <= 80 THEN 'B' ELSE 'C' END curva_abc FROM (SELECT category, rank() OVER (PARTITION BY category ORDER BY sales DESC) AS rank, title, sales, TRUNC((sum(sales) OVER (PARTITION BY category ORDER BY sales DESC) / sum(sales) OVER (PARTITION BY category))*100,1) AS porcentagem FROM sales_title) i ORDER BY category, rank; Window Functions
49. AGENDA SQL Avançado com PostgreSQL Subconsultas INSERT ON CONFLICT DML c/ RETURNING Full Text Search (FTS) Foreign Data Wrapper Window Function
50. Fábio Telles Rodriguez • email: telles@timbira.com.br • telegram: fabio_telles • LinkedIn: https://www.linkedin.com/in/telles Comunidade Brasileira de PostgreSQL • PostgreSQL no Telegram: https://t.me/postgresqlbr • Eventos de PostgreSQL no Telegram: https://t.me/pgconfbr • PGDays 2023: https://pgday.com.br
51. CURTIU? SEGUE A GENTE!