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

SQL Avançado com PostgreSQL

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
Tweet

More Decks by Fábio Telles Rodriguez

Other Decks in Technology

Transcript

  1. SQL Avançado com PostgreSQL

    View full-size slide

  2. AGENDA
    SQL Avançado com PostgreSQL
    Subconsultas
    INSERT ON CONFLICT
    DML c/ RETURNING
    Full Text Search (FTS)
    Foreign Data Wrapper
    Window Function

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  13. AGENDA
    SQL Avançado com PostgreSQL
    Subconsultas
    INSERT ON CONFLICT
    DML c/ RETURNING
    Full Text Search (FTS)
    Foreign Data Wrapper
    Window Function

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  17. AGENDA
    SQL Avançado com PostgreSQL
    Subconsultas
    INSERT ON CONFLICT
    DML c/ RETURNING
    Full Text Search (FTS)
    Foreign Data Wrapper
    Window Function

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  23. AGENDA
    SQL Avançado com PostgreSQL
    Subconsultas
    INSERT ON CONFLICT
    DML c/ RETURNING
    Full Text Search (FTS)
    Foreign Data Wrapper
    Window Function

    View full-size slide

  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)

    View full-size slide

  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)

    View full-size slide

  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)

    View full-size slide

  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)

    View full-size slide

  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)

    View full-size slide

  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)

    View full-size slide

  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)

    View full-size slide

  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)

    View full-size slide

  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)

    View full-size slide

  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)

    View full-size slide

  34. AGENDA
    SQL Avançado com PostgreSQL
    Subconsultas
    INSERT ON CONFLICT
    DML c/ RETURNING
    Full Text Search (FTS)
    Foreign Data Wrapper
    Window Function

    View full-size slide

  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)

    View full-size slide

  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)

    View full-size slide

  37. AGENDA
    SQL Avançado com PostgreSQL
    Subconsultas
    INSERT ON CONFLICT
    DML c/ RETURNING
    Full Text Search (FTS)
    Foreign Data Wrapper
    Window Function

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  40. SELECT sum(sales) AS total_sales
    FROM sales_title;
    SELECT sum(sales) OVER () AS total_sales
    FROM sales_title;
    Window Functions

    View full-size slide

  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

    View full-size slide

  42. SELECT
    title,
    category,
    sum(sales) OVER (partition by category) AS
    sales_category,
    sum(sales) OVER () AS sales_total
    FROM sales_title;
    Window Functions

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

  45. SELECT
    title,
    category,
    sales,
    rank() OVER (PARTITION BY category ORDER BY sales DESC) AS rank
    FROM sales_title;
    Window Functions

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  49. AGENDA
    SQL Avançado com PostgreSQL
    Subconsultas
    INSERT ON CONFLICT
    DML c/ RETURNING
    Full Text Search (FTS)
    Foreign Data Wrapper
    Window Function

    View full-size slide

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

    View full-size slide

  51. CURTIU?
    SEGUE A GENTE!

    View full-size slide