Share
Embed
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
-
AGENDA SQL Avançado com PostgreSQL Subconsultas INSERT ON CONFLICT DML
c/ RETURNING Full Text Search (FTS) Foreign Data Wrapper Window Function -
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 -
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 -
[ 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 -
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 -
[ 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 -
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 -
[ 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 -
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 -
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 -
[ 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 -
AGENDA SQL Avançado com PostgreSQL Subconsultas INSERT ON CONFLICT DML
c/ RETURNING Full Text Search (FTS) Foreign Data Wrapper Window Function -
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 -
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 -
[ 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 -
AGENDA SQL Avançado com PostgreSQL Subconsultas INSERT ON CONFLICT DML
c/ RETURNING Full Text Search (FTS) Foreign Data Wrapper Window Function -
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 -
[ 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 -
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 -
[ 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 -
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 -
AGENDA SQL Avançado com PostgreSQL Subconsultas INSERT ON CONFLICT DML
c/ RETURNING Full Text Search (FTS) Foreign Data Wrapper Window Function -
• 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) -
• 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) -
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) -
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) -
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) -
• 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) -
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) -
• 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) -
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) -
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) -
AGENDA SQL Avançado com PostgreSQL Subconsultas INSERT ON CONFLICT DML
c/ RETURNING Full Text Search (FTS) Foreign Data Wrapper Window Function -
• 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) -
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) -
AGENDA SQL Avançado com PostgreSQL Subconsultas INSERT ON CONFLICT DML
c/ RETURNING Full Text Search (FTS) Foreign Data Wrapper Window Function -
• 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 -
• 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 -
SELECT sum(sales) AS total_sales FROM sales_title; SELECT sum(sales) OVER ()
AS total_sales FROM sales_title; Window Functions -
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 -
SELECT title, category, sum(sales) OVER (partition by category) AS sales_category,
sum(sales) OVER () AS sales_total FROM sales_title; Window Functions -
SELECT title, category, sales, rank() OVER (ORDER BY sales DESC)
AS rank FROM sales_title; Window Functions -
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 -
SELECT title, category, sales, rank() OVER (PARTITION BY category ORDER
BY sales DESC) AS rank FROM sales_title; Window Functions -
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 -
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 -
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 -
AGENDA SQL Avançado com PostgreSQL Subconsultas INSERT ON CONFLICT DML
c/ RETURNING Full Text Search (FTS) Foreign Data Wrapper Window Function -
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