PostgreSQL Query Syntax Basics

Development Environment Setup

• PostgreSQL 16.1
• pgAdmin 4
• DBeaver Community
  • Cross-platform database tool
  • Supports many different databases
  • Free and open source

Resources

• Official documentation: https://www.postgresql.org/docs/current/
• SQL syntax reference: https://www.postgresqltutorial.com/
• Video tutorial: https://www.youtube.com/watch?v=qw--VYLpxG4

Basic Query Syntax

SELECT Statement Basics

1. Select all columns

   SELECT * FROM employees;
   

2. Select specific columns

   SELECT first_name, last_name, salary FROM employees;
   

3. Use column aliases

   SELECT first_name AS name, last_name AS surname FROM employees;
   

Filtering with WHERE

1. Basic comparison operators

   SELECT * FROM employees WHERE salary > 5000;
   SELECT * FROM products WHERE price BETWEEN 10 AND 20;
   SELECT * FROM customers WHERE country IN ('USA', 'Canada', 'UK');
   

2. Pattern matching

   SELECT * FROM employees WHERE last_name LIKE 'S%';
   SELECT * FROM products WHERE description LIKE '%organic%';
   

3. Handling NULL values

   SELECT * FROM employees WHERE manager_id IS NULL;
   SELECT * FROM orders WHERE shipping_date IS NOT NULL;
   

Sorting and Limiting Results

1. The ORDER BY clause

   SELECT * FROM employees ORDER BY salary DESC;
   SELECT * FROM products ORDER BY category ASC, price DESC;
   

2. LIMIT and OFFSET

   SELECT * FROM employees ORDER BY salary DESC LIMIT 10;
   SELECT * FROM employees ORDER BY hire_date LIMIT 5 OFFSET 10;
   

Advanced Query Techniques

Aggregate Functions

1. Common aggregate functions

   SELECT COUNT(*) FROM employees;
   SELECT AVG(salary) FROM employees;
   SELECT MAX(price), MIN(price) FROM products;
   SELECT SUM(amount) FROM orders;
   

2. Grouping with GROUP BY

   SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;
   SELECT category, AVG(price) AS avg_price FROM products GROUP BY category;
   

3. Filtering groups with HAVING

   SELECT department_id, AVG(salary) FROM employees 
   GROUP BY department_id HAVING AVG(salary) > 5000;
   

Multi-Table Queries

1. Joining tables

   SELECT e.first_name, e.last_name, d.name AS department
   FROM employees e
   JOIN departments d ON e.department_id = d.id;
   

2. Different types of joins

   -- 内连接(INNER JOIN)
   SELECT c.name, o.order_date
   FROM customers c
   INNER JOIN orders o ON c.id = o.customer_id;
   
   -- 左连接(LEFT JOIN)
   SELECT c.name, o.order_date
   FROM customers c
   LEFT JOIN orders o ON c.id = o.customer_id;
   
   -- 右连接(RIGHT JOIN)
   SELECT e.first_name, d.name
   FROM employees e
   RIGHT JOIN departments d ON e.department_id = d.id;
   

PostgreSQL-Specific Features

Data Types and Operators

1. Working with JSON

   SELECT data->>'name' AS customer_name 
   FROM orders WHERE data->>'status' = 'delivered';
   

2. Working with arrays

   SELECT * FROM products WHERE tags @> ARRAY['organic'];
   SELECT ARRAY_LENGTH(phone_numbers, 1) FROM contacts;
   

3. Full-text search

   SELECT * FROM articles
   WHERE to_tsvector('english', content) @@ to_tsquery('postgresql & tutorial');
   

Commonly Used Functions

1. String functions

   SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
   SELECT UPPER(name) FROM products;
   SELECT LENGTH(description) FROM products;
   

2. Date functions

   SELECT NOW();
   SELECT DATE_TRUNC('month', order_date) AS month, COUNT(*) 
   FROM orders GROUP BY month;
   SELECT AGE(NOW(), hire_date) FROM employees;
   

Practical Tips

Transaction Control

1. Basic transactions

   BEGIN;
   UPDATE accounts SET balance = balance - 100 WHERE id = 1;
   UPDATE accounts SET balance = balance + 100 WHERE id = 2;
   COMMIT;
   

2. Savepoints

   BEGIN;
   INSERT INTO orders (customer_id, amount) VALUES (1, 100);
   SAVEPOINT after_order;
   INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 1, 2);
   ROLLBACK TO after_order;
   COMMIT;
   

Performance Optimization

1. Using indexes

   CREATE INDEX idx_employees_last_name ON employees (last_name);
   EXPLAIN ANALYZE SELECT * FROM employees WHERE last_name = 'Smith';
   

2. Analyzing query plans

   EXPLAIN ANALYZE
   SELECT c.name, COUNT(o.id) 
   FROM customers c
   JOIN orders o ON c.id = o.customer_id
   GROUP BY c.name;