pq初了解

安装

docker还是快的

docker pull postgres
mkdir /myData/postgresql


docker run --name postgres \
    --restart=always \
    -e POSTGRES_PASSWORD=root \
    -p 5432:5432 \
    -v /myData/postgresql:/var/lib/postgresql/data \
    -d postgres

# 查看
docker ps

PostgreSQL的优点

• 完全开源，不像MySQL被收购后，搞社区版和商业版
• 高级功能：我看中的点。支持如自定义数据类型、自定义函数、复杂查询和JSON数据类型。
• 因为功能的差异和性能特点，PQ更适用于需要高级功能和复杂查询的场景，如大规模企业应用和数据仓库。而MySQL适用于简单查询和快速读写操作的场景，如Web应用程序和小型项目。(那我拿来写oj可能有点牛刀小试了)

复杂查询支持

• 窗口函数
  低版本mysql不支持我还是有点膈应

  SELECT name, department, salary, AVG(salary) 
  OVER (
  	PARTITION BY department
  ) AS avg_salary 
  FROM employees;

• WITH创建临时表并可复用

  WITH temp_table AS (
    SELECT id, name, salary FROM employees WHERE department = 'Finance'
  )
  SELECT * FROM temp_table;

• 递归查询(mysql不支持)

  WITH RECURSIVE employee_tree AS (
    SELECT id, name, manager_id, 0 AS level FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, et.level + 1 FROM employees e INNER JOIN employee_tree et ON e.manager_id = et.id
  )
  SELECT id, name, level FROM employee_tree;

索引支持

• 默认B+树
• hash索引：只支持等值查找，处理大量等值查找时性能优秀
• GIN：倒排索引，适用于包含数组、JSON和其他复杂数据类型的查询。基于倒排索引允许处理多值列。
• GIST：相似度匹配、全文搜索、空间数据。
• BRIN、SP-GIST…..

场景：每个文字都有多个标签

CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title VARCHAR(100),
  tags TEXT[]
);

INSERT INTO articles (title, tags)
VALUES
  ('Getting Started with PostgreSQL', ARRAY['database', 'PostgreSQL', 'tutorial']),
  ('Introduction to Machine Learning', ARRAY['machine learning', 'data science']),
  ('Web Development Best Practices', ARRAY['web development', 'programming', 'best practices']);

1. 查询包含特定标签的文章：

   SELECT * FROM articles WHERE 'web development' = ANY(tags);

2. 查询包含多个标签的文章：

   SELECT * FROM articles WHERE ARRAY['database', 'PostgreSQL'] <@ tags;

3. 查询具有特定标签的文章数量：

   SELECT COUNT(*) FROM articles WHERE 'programming' = ANY(tags);