PostgreSQL Syntax / PostgreSQL语法 / PostgreSQL Syntax / PostgreSQL语法

PostgreSQL Syntax / PostgreSQL语法

SQL Overview / SQL概述

SQL (Structured Query Language) is the standard language for managing and manipulating relational databases. PostgreSQL follows the SQL standard closely while extending it with powerful additional features.

SQL（结构化查询语言）是管理和操作关系数据库的标准语言。PostgreSQL紧跟SQL标准，同时通过强大的附加功能对其进行了扩展。

SQL Command Types / SQL命令类型

PostgreSQL SQL commands are categorized into several types:

PostgreSQL SQL命令分为几种类型：

1. Data Definition Language (DDL) / 数据定义语言（DDL）

DDL commands define and modify database structures:

DDL命令定义和修改数据库结构：

-- Create database objects
CREATE DATABASE myapp;
CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(100));
CREATE INDEX idx_users_name ON users(name);
CREATE VIEW user_summary AS SELECT id, name FROM users;

-- Modify database objects
ALTER TABLE users ADD COLUMN email VARCHAR(200);
ALTER TABLE users RENAME COLUMN name TO username;
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(300);

-- Delete database objects
DROP TABLE users;
DROP DATABASE myapp;
DROP INDEX idx_users_name;
DROP VIEW user_summary;

2. Data Manipulation Language (DML) / 数据操作语言（DML）

DML commands manipulate data within tables:

DML命令操作表中的数据：

-- Insert data
INSERT INTO users (name, email) VALUES ('John', 'john@example.com');
INSERT INTO users (name, email) VALUES ('Jane', 'jane@example.com'), ('Bob', 'bob@example.com');

-- Update data
UPDATE users SET email = 'john.doe@example.com' WHERE name = 'John';

-- Delete data
DELETE FROM users WHERE id = 1;

-- Query data
SELECT * FROM users;
SELECT name, email FROM users WHERE is_active = TRUE;

3. Data Control Language (DCL) / 数据控制语言（DCL）

DCL commands control access to data:

DCL命令控制对数据的访问：

-- Grant privileges
GRANT SELECT, INSERT ON users TO 'app_user';
GRANT ALL PRIVILEGES ON DATABASE myapp TO 'admin_role';

-- Revoke privileges
REVOKE INSERT ON users FROM 'app_user';
REVOKE ALL PRIVILEGES ON users FROM 'guest';

4. Transaction Control Language (TCL) / 事务控制语言（TCL）

TCL commands manage database transactions:

TCL命令管理数据库事务：

-- Start transaction
BEGIN;

-- Perform operations
INSERT INTO users (name) VALUES ('Alice');
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Commit or rollback
COMMIT;  -- Save all changes
-- ROLLBACK;  -- Undo all changes

-- Savepoint example
SAVEPOINT before_update;
-- ... operations ...
ROLLBACK TO SAVEPOINT before_update;
RELEASE SAVEPOINT before_update;

Basic Syntax Rules / 基本语法规则

Case Sensitivity / 大小写敏感性

PostgreSQL is case-insensitive for keywords but case-sensitive for identifiers (when quoted):

PostgreSQL对关键字不区分大小写，但对标识符（当使用引号时）区分大小写：

-- Keywords are case-insensitive
SELECT * FROM users;
select * from users;
SeLeCt * FrOm users;

-- Unquoted identifiers are case-insensitive
SELECT * FROM Users;  -- Same as 'users'
SELECT * FROM USERS;  -- Same as 'users'

-- Quoted identifiers are case-sensitive
SELECT * FROM "Users";  -- Different from 'users'
SELECT * FROM "users";  -- Original table name

Whitespace / 空白字符

Whitespace (spaces, tabs, newlines) is generally ignored:

空白字符（空格、制表符、换行符）通常被忽略：

-- All these are equivalent
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE id=1;
SELECT * FROM users WHERE 
    id = 1;

Comments / 注释

PostgreSQL supports three types of comments:

PostgreSQL支持三种类型的注释：

-- Single-line comment

/* Multi-line comment
   spanning multiple lines */

/* 
 * PL/pgSQL style block comment
 * Used in stored procedures
 */

Statements and Clauses / 语句和子句

A SQL statement consists of one or more clauses:

SQL语句由一个或多个子句组成：

SELECT column1, column2           -- SELECT clause
FROM table_name                   -- FROM clause
WHERE condition                   -- WHERE clause
ORDER BY column1 ASC              -- ORDER BY clause
LIMIT 10;                         -- LIMIT clause

Identifiers / 标识符

Identifiers are names used to identify database objects:

标识符是用于识别数据库对象的名称：

Types of Identifiers / 标识符类型

1. Regular Identifiers / 常规标识符

Must start with a letter or underscore, followed by letters, digits, or underscores:

必须以字母或下划线开头，后跟字母、数字或下划线：

-- Valid identifiers
users
user_name
users123
_users

-- Invalid identifiers
123users    -- Starts with digit
user-name   -- Contains hyphen
user.name   -- Contains period

2. Delimited Identifiers / 带分隔符的标识符

Used for identifiers that don't follow regular identifier rules or require exact case:

用于不符合常规标识符规则或需要精确大小写的标识符：

-- Using double quotes for special identifiers
SELECT * FROM "my-table";
SELECT * FROM "My Table";
SELECT * FROM "select";

-- Using in schema definitions
CREATE TABLE "Order-Details" (
    "Order ID" INT,
    "Product Name" VARCHAR(100)
);

Qualified Names / 限定名称

Use dot notation to specify schema-qualified names:

使用点号表示法指定模式限定名称：

-- Schema-qualified table name
SELECT * FROM public.users;
SELECT * FROM schema_name.users;

-- Column qualified with table name
SELECT users.id, users.name FROM users;

-- Column qualified with schema and table
SELECT public.users.id FROM public.users;

-- Database-qualified name
SELECT * FROM database_name.schema_name.table_name;

Data Types / 数据类型

PostgreSQL supports a wide range of data types:

PostgreSQL支持广泛的数据类型：

Numeric Types / 数值类型

-- Integer types
CREATE TABLE numbers (
    small_int SMALLINT,      -- 2 bytes: -32768 to 32767
    regular_int INTEGER,     -- 4 bytes: -2147483648 to 2147483647
    big_int BIGINT,          -- 8 bytes: -9223372036854775808 to 9223372036854775807
    small_serial SMALLSERIAL,
    regular_serial SERIAL,   -- Auto-incrementing integer
    big_serial BIGSERIAL     -- Auto-incrementing bigint
);

-- Floating-point types
CREATE TABLE floats (
    real_num REAL,           -- 4 bytes, single precision
    double_num DOUBLE PRECISION,  -- 8 bytes, double precision
    decimal_num DECIMAL(10, 2),   -- Exact numeric, 10 digits, 2 decimal places
    numeric_num NUMERIC(10, 5)    -- Same as DECIMAL
);

Character Types / 字符类型

-- Character strings
CREATE TABLE strings (
    fixed_char CHAR(10),         -- Fixed-length, padded with spaces
    varchar_str VARCHAR(100),    -- Variable-length, max 100 chars
    text_str TEXT                -- Variable-length, unlimited length
);

-- Examples
INSERT INTO strings VALUES ('hello   ', 'hello', 'Hello, world!');
SELECT CONCAT(fixed_char, '|', varchar_str, '|', text_str) FROM strings;

Date/Time Types / 日期/时间类型

-- Date and time types
CREATE TABLE time_data (
    just_date DATE,              -- Date only (no time)
    just_time TIME,              -- Time only (no date)
    timestamp_tz TIMESTAMP WITH TIME ZONE,  -- Date + time with timezone
    timestamp_no_tz TIMESTAMP WITHOUT TIME ZONE,  -- Date + time without timezone
    interval_val INTERVAL        -- Time interval
);

-- Examples
INSERT INTO time_data VALUES 
    ('2024-01-15', '14:30:00', '2024-01-15 14:30:00+00', NOW(), '1 year 2 months');

Boolean Type / 布尔类型

-- Boolean type
CREATE TABLE flags (
    is_active BOOLEAN,
    has_permission BOOLEAN,
    is_verified BOOLEAN
);

-- Boolean values
INSERT INTO flags VALUES 
    (TRUE, FALSE, TRUE),
    ('yes', 'no', 'true'),
    (1, 0, 'y');

Array Types / 数组类型

-- Array types
CREATE TABLE arrays (
    int_array INTEGER[],
    text_array TEXT[],
    dim_array INTEGER[3][3]
);

-- Array operations
INSERT INTO arrays VALUES 
    ('{1, 2, 3}', ARRAY['apple', 'banana', 'cherry']),
    (ARRAY[4, 5, 6], ARRAY['dog', 'cat']);

-- Array functions
SELECT int_array[1] FROM arrays;                    -- Access element
SELECT UNNEST(int_array) FROM arrays;               -- Expand array
SELECT ARRAY_LENGTH(int_array, 1) FROM arrays;      -- Get length

JSON Types / JSON类型

-- JSON types
CREATE TABLE json_data (
    json_doc JSON,          -- Validates JSON syntax
    jsonb_doc JSONB        -- Binary format, faster queries
);

-- JSON operations
INSERT INTO json_data VALUES 
    ('{"name": "John", "age": 30}', '{"city": "NYC", "active": true}');

-- JSON extraction
SELECT json_doc->'name' AS name FROM json_data;           -- Get value as JSON
SELECT json_doc->>'name' AS name FROM json_data;          -- Get value as text
SELECT jsonb_doc->'city' AS city FROM json_data;

UUID Type / UUID类型

-- UUID type
CREATE TABLE uuid_demo (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    ref_id UUID
);

-- Insert with UUID
INSERT INTO uuid_demo (ref_id) VALUES 
    ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'),
    (gen_random_uuid());

Expressions / 表达式

Expressions are combinations of values and operators that compute a value:

表达式是值的组合和运算符，用于计算一个值：

Arithmetic Expressions / 算术表达式

-- Basic arithmetic
SELECT 10 + 5 AS addition;        -- 15
SELECT 10 - 5 AS subtraction;     -- 5
SELECT 10 * 5 AS multiplication;  -- 50
SELECT 10 / 5 AS division;        -- 2
SELECT 10 % 3 AS modulus;         -- 1
SELECT 2 ^ 3 AS exponentiation;   -- 8

-- In queries
SELECT 
    price * quantity AS total,
    price * 0.1 AS tax,
    price + tax AS total_with_tax
FROM products;

String Expressions / 字符串表达式

-- String concatenation
SELECT 'Hello' || ' ' || 'World' AS greeting;      -- 'Hello World'
SELECT CONCAT('Hello', ' ', 'World');              -- 'Hello World'

-- String functions
SELECT LENGTH('Hello World');                      -- 11
SELECT UPPER('hello');                             -- 'HELLO'
SELECT LOWER('HELLO');                             -- 'hello'
SELECT TRIM('  hello  ');                          -- 'hello'
SELECT SUBSTRING('Hello World', 1, 5);             -- 'Hello'
SELECT REPLACE('Hello World', 'World', 'PostgreSQL'); -- 'Hello PostgreSQL'

Boolean Expressions / 布尔表达式

-- Comparison operators
SELECT 10 > 5;          -- TRUE
SELECT 10 < 5;          -- FALSE
SELECT 10 = 10;         -- TRUE
SELECT 10 <> 5;         -- TRUE (not equal)
SELECT 10 != 5;         -- TRUE (not equal)
SELECT 10 >= 10;        -- TRUE
SELECT 10 <= 5;         -- FALSE

-- Logical operators
SELECT TRUE AND TRUE;   -- TRUE
SELECT TRUE AND FALSE;  -- FALSE
SELECT TRUE OR FALSE;   -- TRUE
SELECT NOT TRUE;        -- FALSE

Conditional Expressions / 条件表达式

-- CASE expression
SELECT 
    CASE 
        WHEN age < 18 THEN 'Minor'
        WHEN age < 65 THEN 'Adult'
        ELSE 'Senior'
    END AS age_group
FROM users;

-- Simple CASE
SELECT 
    CASE status
        WHEN 'pending' THEN 'Waiting'
        WHEN 'active' THEN 'In Progress'
        WHEN 'completed' THEN 'Done'
        ELSE 'Unknown'
    END AS status_text
FROM orders;

-- COALESCE - returns first non-null value
SELECT COALESCE(email, 'no email') FROM users;

-- NULLIF - returns NULL if arguments are equal
SELECT NULLIF(price, 0) FROM products;

-- GREATEST and LEAST
SELECT GREATEST(10, 20, 15);    -- 20
SELECT LEAST(10, 20, 15);       -- 10

Subquery Expressions / 子查询表达式

-- EXISTS
SELECT * FROM users 
WHERE EXISTS (
    SELECT 1 FROM orders WHERE orders.user_id = users.id
);

-- IN with subquery
SELECT * FROM products 
WHERE category_id IN (
    SELECT id FROM categories WHERE name IN ('Electronics', 'Books')
);

-- ANY/SOME
SELECT * FROM products 
WHERE price > ANY (
    SELECT AVG(price) FROM products GROUP BY category_id
);

-- ALL
SELECT * FROM products 
WHERE price > ALL (
    SELECT AVG(price) FROM products GROUP BY category_id
);

Operators / 运算符

Comparison Operators / 比较运算符

-- Standard comparisons
SELECT * FROM users WHERE age = 30;
SELECT * FROM users WHERE age <> 30;
SELECT * FROM users WHERE age != 30;
SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE age >= 18;
SELECT * FROM users WHERE age < 65;
SELECT * FROM users WHERE age <= 65;

-- NULL-safe comparisons
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;
SELECT * FROM users WHERE email IS DISTINCT FROM '';  -- Treats NULL as distinct

Logical Operators / 逻辑运算符

-- AND operator
SELECT * FROM users 
WHERE age >= 18 AND is_active = TRUE;

-- OR operator
SELECT * FROM users 
WHERE status = 'premium' OR status = 'vip';

-- NOT operator
SELECT * FROM users 
WHERE NOT is_active;

-- Combined
SELECT * FROM users 
WHERE (age >= 18 AND age <= 65) OR status = 'admin';

Pattern Matching / 模式匹配

-- LIKE (case-sensitive)
SELECT * FROM users WHERE name LIKE 'J%';        -- Starts with J
SELECT * FROM users WHERE name LIKE '%ohn';      -- Ends with ohn
SELECT * FROM users WHERE name LIKE '%oh%';      -- Contains oh

-- ILIKE (case-insensitive)
SELECT * FROM users WHERE name ILIKE 'j%';       -- Case-insensitive

-- SIMILAR TO (POSIX regex)
SELECT * FROM users WHERE name SIMILAR TO '(John|Jane)%';

-- ~ (POSIX regex, case-sensitive)
SELECT * FROM users WHERE name ~ '^J';
SELECT * FROM users WHERE name ~ '[0-9]';

-- ~* (POSIX regex, case-insensitive)
SELECT * FROM users WHERE name ~* '^j';

Mathematical Operators / 数学运算符

-- Basic operators
SELECT 10 + 5;    -- Addition
SELECT 10 - 5;    -- Subtraction
SELECT 10 * 5;    -- Multiplication
SELECT 10 / 5;    -- Division
SELECT 10 % 3;    -- Modulo
SELECT 2 ^ 3;     -- Exponentiation
SELECT |/ 9;      -- Square root
SELECT ||/ 27;    -- Cube root
SELECT factorial(5);  -- Factorial
SELECT binomial(10, 2);  -- Binomial coefficient

String Operators / 字符串运算符

-- Concatenation
SELECT 'Hello' || ' World';              -- 'Hello World'
SELECT CONCAT('Hello', ' ', 'World');    -- 'Hello World'

-- Bitwise operations
SELECT 65 | 32;     -- Bitwise OR
SELECT 65 & 32;     -- Bitwise AND
SELECT ~65;         -- Bitwise NOT
SELECT 65 << 2;     -- Bitwise shift left
SELECT 65 >> 2;     -- Bitwise shift right

SQL Statement Structure / SQL语句结构

SELECT Statement / SELECT语句

SELECT [DISTINCT] column1, column2, ...
FROM table_name
[WHERE condition]
[GROUP BY column1, column2, ...]
[HAVING condition]
[ORDER BY column1 [ASC | DESC], ...]
[LIMIT number | ALL]
[OFFSET number];

INSERT Statement / INSERT语句

-- Single row
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

-- Multiple rows
INSERT INTO table_name (column1, column2, ...)
VALUES 
    (value1, value2, ...),
    (value3, value4, ...),
    (value5, value6, ...);

-- From SELECT
INSERT INTO table_name (column1, column2, ...)
SELECT column1, column2, ...
FROM another_table
WHERE condition;

UPDATE Statement / UPDATE语句

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

DELETE Statement / DELETE语句

DELETE FROM table_name
WHERE condition;

CREATE Statement / CREATE语句

CREATE DATABASE database_name;

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
);

ALTER Statement / ALTER语句

ALTER TABLE table_name
ADD COLUMN column_name datatype;

ALTER TABLE table_name
DROP COLUMN column_name;

ALTER TABLE table_name
RENAME COLUMN old_name TO new_name;

ALTER TABLE table_name
ALTER COLUMN column_name TYPE new_datatype;

DROP Statement / DROP语句

DROP DATABASE database_name;
DROP TABLE table_name;
DROP TABLE IF EXISTS table_name;

Naming Conventions / 命名约定

Recommended Practices / 建议的做法

-- Use lowercase for table and column names
CREATE TABLE users (
    user_id INTEGER,
    user_name VARCHAR(100),
    created_at TIMESTAMP
);

-- Use underscores for multi-word names
CREATE TABLE customer_orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER,
    order_date DATE,
    total_amount DECIMAL(10, 2)
);

-- Use singular names for tables
CREATE TABLE user ();       -- Preferred
-- CREATE TABLE users ();   -- Also acceptable

-- Use descriptive names
CREATE TABLE product_categories ();   -- Clear
-- CREATE TABLE pc ();      -- Not recommended

Reserved Words / 保留字

Avoid using PostgreSQL reserved words as identifiers:

避免将PostgreSQL保留字用作标识符：

-- Cannot use directly (need quotes)
SELECT * FROM "select";        -- "select" is reserved
SELECT * FROM "order";         -- "order" is reserved

-- Safe alternatives
SELECT * FROM items;           -- "item" is not reserved
SELECT * FROM orders;          -- "orders" is not reserved

Common reserved words include: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, TABLE, INDEX, VIEW, DATABASE, SCHEMA, USER, GROUP, etc.

常见保留字包括：SELECT、INSERT、UPDATE、DELETE、CREATE、DROP、ALTER、TABLE、INDEX、VIEW、DATABASE、SCHEMA、USER、GROUP等。

Best Practices / 最佳实践

Write Clear SQL / 编写清晰的SQL

-- Good: Clear formatting
SELECT 
    u.id,
    u.name,
    COUNT(o.id) AS order_count,
    SUM(o.total) AS total_spent
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2023-01-01'
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 5
ORDER BY total_spent DESC
LIMIT 10;

-- Avoid: All on one line
SELECT u.id,u.name,COUNT(o.id) AS order_count FROM users u INNER JOIN orders o ON u.id=o.user_id WHERE u.created_at>='2023-01-01' GROUP BY u.id,u.name HAVING COUNT(o.id)>5 ORDER BY total_spent DESC LIMIT 10;

Use Appropriate Data Types / 使用适当的数据类型

-- Good: Appropriate data types
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INTEGER NOT NULL,
    is_available BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Avoid: Poor data type choices
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,  -- Too long, use VARCHAR
    price TEXT,  -- Wrong type for numbers
    stock_quantity NUMERIC(30),  -- Overkill
    is_available TEXT,  -- Should be BOOLEAN
    created_at TEXT  -- Should be TIMESTAMP
);

Use Constraints / 使用约束

-- Good: Proper constraints
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash CHAR(64) NOT NULL,
    role VARCHAR(50) DEFAULT 'user' CHECK (role IN ('user', 'admin', 'moderator')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Avoid: No constraints
CREATE TABLE users (
    id INTEGER,
    email VARCHAR(255),
    password_hash VARCHAR(255),
    role VARCHAR(50),
    created_at TIMESTAMP
);

Summary / 小结

Understanding PostgreSQL syntax is fundamental to working effectively with the database:

理解PostgreSQL语法是有效使用数据库的基础：

• SQL Categories: DDL, DML, DCL, TCL / SQL分类：DDL、DML、DCL、TCL
• Syntax Rules: Case sensitivity, whitespace, comments / 语法规则：大小写敏感性、空白字符、注释
• Data Types: Comprehensive type system / 数据类型：全面的类型系统
• Expressions: Arithmetic, string, boolean, conditional / 表达式：算术、字符串、布尔、条件
• Operators: Comparison, logical, pattern matching / 运算符：比较、逻辑、模式匹配
• Best Practices: Clear formatting, proper data types, constraints / 最佳实践：清晰格式、适当的数据类型、约束