Upgrade to Pro — share decks privately, control downloads, hide ads and more …

SQL Avançado com PostgreSQL

Sponsored · Your Podcast. Everywhere. Effortlessly. Share. Educate. Inspire. Entertain. You do you. We'll handle the rest. →

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

Fábio Telles Rodriguez

June 12, 2023

Transcript

51 slides
  1. AGENDA SQL Avançado com PostgreSQL Subconsultas INSERT ON CONFLICT DML

    c/ RETURNING Full Text Search (FTS) Foreign Data Wrapper Window Function
  2. 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
  3. 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
  4. [ 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
  5. 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
  6. [ 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
  7. 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
  8. [ 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
  9. 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
  10. 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
  11. [ 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
  12. AGENDA SQL Avançado com PostgreSQL Subconsultas INSERT ON CONFLICT DML

    c/ RETURNING Full Text Search (FTS) Foreign Data Wrapper Window Function
  13. 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
  14. 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
  15. [ 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
  16. AGENDA SQL Avançado com PostgreSQL Subconsultas INSERT ON CONFLICT DML

    c/ RETURNING Full Text Search (FTS) Foreign Data Wrapper Window Function
  17. 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
  18. [ 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
  19. 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
  20. [ 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
  21. 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
  22. AGENDA SQL Avançado com PostgreSQL Subconsultas INSERT ON CONFLICT DML

    c/ RETURNING Full Text Search (FTS) Foreign Data Wrapper Window Function
  23. • 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)
  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. 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)
  26. 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)
  27. 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)
  28. • 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)
  29. 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)
  30. • 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)
  31. 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)
  32. 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)
  33. AGENDA SQL Avançado com PostgreSQL Subconsultas INSERT ON CONFLICT DML

    c/ RETURNING Full Text Search (FTS) Foreign Data Wrapper Window Function
  34. • 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)
  35. 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)
  36. AGENDA SQL Avançado com PostgreSQL Subconsultas INSERT ON CONFLICT DML

    c/ RETURNING Full Text Search (FTS) Foreign Data Wrapper Window Function
  37. • 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
  38. • 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
  39. SELECT sum(sales) AS total_sales FROM sales_title; SELECT sum(sales) OVER ()

    AS total_sales FROM sales_title; Window Functions
  40. 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
  41. SELECT title, category, sum(sales) OVER (partition by category) AS sales_category,

    sum(sales) OVER () AS sales_total FROM sales_title; Window Functions
  42. SELECT title, category, sales, rank() OVER (ORDER BY sales DESC)

    AS rank FROM sales_title; Window Functions
  43. 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
  44. SELECT title, category, sales, rank() OVER (PARTITION BY category ORDER

    BY sales DESC) AS rank FROM sales_title; Window Functions
  45. 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
  46. 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
  47. 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
  48. AGENDA SQL Avançado com PostgreSQL Subconsultas INSERT ON CONFLICT DML

    c/ RETURNING Full Text Search (FTS) Foreign Data Wrapper Window Function
  49. Fábio Telles Rodriguez • email: [email protected] • 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

More Decks by Fábio Telles Rodriguez

Other Decks in Technology