1. Basic queries: not just SELECT

In Postgres, basic queries also include some more flexible features than standard SQL.

• Alias and calculated fields: You can perform calculations or formatting directly during the query.

SELECT name, (price * stock) AS total_value FROM products;

• DISTINCT ON: This is the magic skill of Postgres. It can remove duplicates based on a certain column while retaining a specific row of another column (for example: the latest order of each user).

-- 获取每个用户最近的一次登录记录
SELECT DISTINCT ON (user_id) user_id, login_time 
FROM user_logins 
ORDER BY user_id, login_time DESC;

• Pattern matching: In addition to LIKE, Postgres supports ~ (regular matching) and ILIKE (case-ignoring matching), which is very friendly to front-end search functions.

2. Queries for specific data types

As a front-end developer, you may be most concerned about how to query JSONB and arrays.

• JSONB query: * ->> gets a text value, @> checks if it contains a JSON structure.

-- 查询 settings 字段中 theme 为 'dark' 的用户
SELECT * FROM users WHERE settings @> '{"theme": "dark"}';

• Array query:
• Use the ANY or && (overlapping) operators.

-- 查询标签中包含 'javascript' 的文章
SELECT * FROM articles WHERE 'javascript' = ANY(tags);

3. Advanced Query: CTE (Common Table Expression)

CTE is the best way to "modularize" complex queries using the WITH keyword.

• Non-recursive CTE: Increase code readability like defining temporary variables.
• Recursive CTE (Recursive): Used to process tree structures (such as menus, organizational structures, comment nesting).

WITH RECURSIVE category_tree AS (
    -- 初始步骤：找到根节点
    SELECT id, name, parent_id FROM categories WHERE parent_id IS NULL
    UNION ALL
    -- 递归步骤：连接子节点
    SELECT c.id, c.name, c.parent_id 
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

4. Window Functions

This is the surefire way to advance Postgres.It allows you to perform calculations on row sets without merging rows (without using GROUP BY).

• Syntax: 函数() OVER (PARTITION BY 分组列 ORDER BY 排序列)
• Commonly used functions:
• RANK() / DENSE_RANK(): Ranking.
• ROW_NUMBER(): Number the row.
• LAG() / LEAD(): Access the "previous row" or "next row" data of the current row (ideal for calculating chain growth).

5. Views & Materialized Views

• View: Virtual table.It just saves a piece of SQL logic, and the SQL behind it will be executed in real time every time the view is queried.
• Materialized View: Actual table.It will query the results physical storage on disk.
• Advantages: Greatly improves the response speed of complex aggregation queries.
• Disadvantages: The data is not real-time and needs to be updated manually or regularly through REFRESH MATERIALIZED VIEW.
• Scenario: Dashboard reports, historical data analysis.

6. Aggregation and filtering (Advanced Aggregation)

Postgres allows the use of the FILTER statement inside aggregate functions, which is more concise than the traditional CASE WHEN.

SELECT 
    count(*) AS total_orders,
    count(*) FILTER (WHERE status = 'completed') AS success_orders,
    count(*) FILTER (WHERE status = 'cancelled') AS failed_orders
FROM orders;