PostgreSQL Syntax

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.

PostgreSQL SQL commands are categorized into several types:

DDL commands define and modify database structures:

SQL Command Types

SQL Command Types

-- 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;

DML commands manipulate data within tables:

-- 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;

DCL commands control access to data:

-- 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';

TCL commands manage database transactions:

-- 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;

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

Case Sensitivity

Case Sensitivity

-- 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 (spaces, tabs, newlines) is generally ignored:

Whitespace

Whitespace

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

SQL Overview

PostgreSQL supports three types of comments:

Comments

Comments

-- Single-line comment

/* Multi-line comment
   spanning multiple lines */

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

A SQL statement consists of one or more clauses:

Statements and Clauses

Statements and Clauses

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

SQL Command Types

Identifiers are names used to identify database objects:

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

Types of Identifiers

Types of Identifiers

-- Valid identifiers
users
user_name
users123
_users

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

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)
);

Use dot notation to specify schema-qualified names:

Qualified Names

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;

PostgreSQL supports a wide range of data types:

Numeric Types

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 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

Date

-- 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

-- 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

-- 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

Basic Syntax Rules

JSON Types

JSON Types

-- 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;

Case Sensitivity

UUID Type

UUID Type

-- 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());

Whitespace

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

Arithmetic Expressions

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 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

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

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

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
);

Comments

Comparison 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

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

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

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

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

Statements and Clauses

SELECT Statement

SELECT Statement

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];

Identifiers

INSERT Statement

INSERT Statement

-- 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;

Types of Identifiers

UPDATE Statement

UPDATE Statement

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

Qualified Names

DELETE Statement

DELETE Statement

DELETE FROM table_name
WHERE condition;

Data Types

CREATE Statement

CREATE Statement

CREATE DATABASE database_name;

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

Numeric Types

ALTER Statement

ALTER Statement

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;

Character Types

DROP Statement

DROP Statement

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

Recommended Practices

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

Date

Avoid using PostgreSQL reserved words as identifiers:

Reserved Words

Reserved Words

-- 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.

Write Clear SQL

Write Clear 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

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

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
);

Boolean Type

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

• SQL Categories: DDL, DML, DCL, TCL / SQL：DDL、DML、DCL、TCL

Array Types

Continue to Data Types for a detailed exploration of PostgreSQL data types.