Chapter 16: PostgreSQL Syntax
PostgreSQL Syntax.
When people say “PostgreSQL syntax”, they usually mean one (or a mix) of these things:
- The general rules how SQL commands must be written in PostgreSQL (lexical structure, keywords, identifiers, quoting, case sensitivity…)
- The overall shape/structure of the most important SQL statements (SELECT, INSERT, UPDATE, DELETE, CREATE TABLE…)
- PostgreSQL-specific extensions to standard SQL syntax (things MySQL or SQLite don’t have)
I’m going to explain all three levels like we’re building it from zero — very detailed, with many small examples, real output feelings, and 2026 PostgreSQL 18.x reality.
Level 1 – The absolute basics: Lexical Structure (how PostgreSQL reads your code)
PostgreSQL follows very strict rules about how it parses (understands) what you write.
a) Commands end with semicolon ;
SQL
|
0 1 2 3 4 5 6 7 |
SELECT * FROM students -- wrong (no ;) SELECT * FROM students; -- correct |
Many beginners forget the ; — error: syntax error at end of input
b) Case sensitivity — almost everything is folded to lowercase
SQL
|
0 1 2 3 4 5 6 7 |
SELECT * FROM Students; -- works (becomes students) SELECT * FROM "Students"; -- different table! (quoted = case-sensitive) |
Rule of thumb 2026:
- Never quote table/column names unless you really want case-sensitivity
- Convention: snake_case lowercase for tables & columns
c) Identifiers (table names, column names, aliases)
- Unquoted: letters, digits, underscores — first character must be letter or underscore
- Maximum length: 63 characters (NAMEDATALEN-1)
- Allowed special: only _
Good:
SQL
|
0 1 2 3 4 5 6 7 |
student_grades_2026 total_order_value |
Bad / needs quotes:
SQL
|
0 1 2 3 4 5 6 7 8 |
"2026_orders" -- starts with number "order-value" -- has hyphen "User" -- capital U (if you want exactly "User") |
d) String literals — always single quotes
SQL
|
0 1 2 3 4 5 6 7 |
WHERE name = 'Rahul' -- correct WHERE name = "Rahul" -- wrong (double quotes = identifier) |
Exception: dollar-quoted strings (very useful for functions)
SQL
|
0 1 2 3 4 5 6 |
$$Hello 'world'$$ |
e) Keywords — case insensitive
SQL
|
0 1 2 3 4 5 6 7 |
select * from students; -- works SeLeCt * FrOm students; -- also works |
But don’t do this — write SELECT, FROM, WHERE in uppercase for readability.
Level 2 – Core statement shapes (the syntax most people mean)
Here is a structured overview of the main commands with their typical 2026 PostgreSQL syntax patterns.
| Command | Main Purpose | Very Common Modern Syntax Pattern (PostgreSQL 18 style) | Quick Feeling / Use Case |
|---|---|---|---|
| SELECT | Read data | SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT … OFFSET … | 80% of your daily work |
| INSERT | Add new rows | INSERT INTO … (col1, col2, …) VALUES (v1, v2, …), (v3, v4, …) … ON CONFLICT … DO UPDATE … RETURNING … | Adding products, users |
| UPDATE | Change existing rows | UPDATE … SET col1 = val1, col2 = val2 … FROM … WHERE … RETURNING … | Change status, price |
| DELETE | Remove rows | DELETE FROM … WHERE … RETURNING … | Cleanup old records |
| CREATE TABLE | Create new table | CREATE TABLE … (id BIGSERIAL PRIMARY KEY, … JSONB, … GENERATED ALWAYS AS …, … CONSTRAINT …) | Schema design |
| ALTER TABLE | Modify existing table | ALTER TABLE … ADD COLUMN …, ALTER COLUMN … TYPE … USING …, DROP COLUMN …, ADD CONSTRAINT … | Schema evolution |
| DROP TABLE | Delete table completely | DROP TABLE … [IF EXISTS] [CASCADE] | Cleanup |
Detailed example of each (copy-paste ready – our students table)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
-- SELECT – most flexible & complex SELECT first_name || ' ' || last_name AS full_name, gpa, gpa * 9.5 AS percentage, address->>'city' AS city FROM students WHERE gpa >= 8.5 AND is_active = TRUE AND address @> '{"city": "Hyderabad"}' ORDER BY gpa DESC LIMIT 5 OFFSET 0; -- INSERT – modern style with RETURNING INSERT INTO students (first_name, last_name, email, gpa, address) VALUES ('Vikram', 'Naik', 'vikram.naik@hyd.edu', 9.15, '{"city": "Hyderabad", "pin": "500072"}') RETURNING id, created_at; -- UPDATE – with calculation & RETURNING old/new UPDATE students SET gpa = ROUND(gpa + 0.3, 2), updated_at = CURRENT_TIMESTAMP WHERE gpa < 8.0 RETURNING email, OLD.gpa AS old_gpa, NEW.gpa AS new_gpa; -- DELETE – safe with RETURNING DELETE FROM students WHERE enrollment_year < 2023 RETURNING first_name, last_name, enrollment_year; -- CREATE TABLE – modern features CREATE TABLE orders ( id BIGSERIAL PRIMARY KEY, customer_id BIGINT NOT NULL REFERENCES customers(id), order_date TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, total NUMERIC(12,2) GENERATED ALWAYS AS IDENTITY, -- wait no, better use trigger or function status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending','processing','shipped','delivered','cancelled')), details JSONB ); -- ALTER TABLE – typical evolution ALTER TABLE students ADD COLUMN IF NOT EXISTS mobile VARCHAR(15), ALTER COLUMN gpa TYPE NUMERIC(4,2), ALTER COLUMN gpa SET DEFAULT 0.00, DROP COLUMN IF EXISTS old_field CASCADE; |
Level 3 – PostgreSQL-specific syntax flavors (things that surprise MySQL users)
| Feature | PostgreSQL syntax example | Why PostgreSQL is different / better |
|---|---|---|
| Serial / Identity | id BIGSERIAL PRIMARY KEY or id BIGINT GENERATED ALWAYS AS IDENTITY | SERIAL is old; identity is SQL standard |
| Generated columns | full_name TEXT GENERATED ALWAYS AS (…) STORED | Computed & stored – MySQL virtual only until recently |
| JSON/JSONB operators | ->, ->>, @>, ?, ?&, #>> | Extremely powerful JSON querying |
| Dollar quoting | $$ function body $$ or $func$ body $func$ | No escaping issues in functions |
| Array literals | ‘{Hyderabad, Secunderabad}’::text[] | Native array support |
| Interval literals | ‘2 years 3 months’::interval | Very natural date math |
| ON CONFLICT (UPSERT) | ON CONFLICT (email) DO UPDATE SET … | Standard way to upsert |
| RETURNING on DML | Almost every INSERT/UPDATE/DELETE can have RETURNING | Get generated IDs, changed values |
| Common Table Expressions (CTE) | WITH cte AS (…) SELECT … FROM cte | Readable complex queries |
| Window functions | RANK() OVER (PARTITION BY … ORDER BY …) | Advanced analytics in pure SQL |
Quick summary table – PostgreSQL syntax cheat-sheet style (2026)
| Category | Key syntax hints | Example snippet |
|---|---|---|
| Statement end | Always ; | SELECT 1; |
| Strings | Single quotes ‘ ‘ | ‘Hyderabad’ |
| Identifiers (safe) | snake_case unquoted | student_grades |
| Date / Time | CURRENT_TIMESTAMP, ‘2026-02-13’::date | CURRENT_DATE + INTERVAL ‘7 days’ |
| JSONB access | ->, ->>, @> | profile->>’city’ |
| Upsert | ON CONFLICT … DO UPDATE | ON CONFLICT (id) DO UPDATE SET … |
| Limit / Pagination | LIMIT n OFFSET m | LIMIT 10 OFFSET 20 |
| Case insensitive match | ILIKE, LOWER() | WHERE name ILIKE ‘%rahul%’ |
This is not everything — PostgreSQL has ~300 pages of syntax rules in the official manual (Chapter 4 + command reference).
But this covers 90% of what you’ll write daily in 2026.
Next class?
Tell me:
- Want deep dive into one command syntax (e.g. full SELECT grammar)?
- PostgreSQL functions & operators cheat-sheet?
- psql meta-commands syntax (\dt, \d+, etc.)?
- Or back to building our demo database with more advanced syntax?
Your guru is ready — what’s next? 🚀
