Mastering PostgreSQL: Essential SQL Commands & Best Practices

PostgreSQL is one of the world's most advanced open‑source relational databases. Understanding its configuration commands, data type conversions, set‑returning functions, and JSONB operators can dramatically improve application performance and code clarity. This guide explores the essential SQL constructs and best practices that every backend developer should master.

⚙️

SET Command

In PostgreSQL, SET modifies configuration parameters (GUC) for the current session or transaction – allowing runtime behavior changes without server restarts.

Why Use SET?

Flexibility: Adjust memory, planner settings, or locale per task.
No downtime: Changes affect only your connection.
Testing: Safely test planner options like enable_seqscan = off.

-- Change date format for current session
SET datestyle = 'SQL, DMY';

-- Increase work memory for a heavy sort
SET work_mem = '64MB';

-- Use SET LOCAL inside a transaction
BEGIN;
SET LOCAL work_mem = '128MB';
-- ... complex query ...
COMMIT; -- automatically reverts

💡 Best Practice: Use SET LOCAL inside transactions to avoid leaking settings to connection pools. Always RESET after tuning.

📅

to_char()

Converts dates, timestamps, or numbers into formatted strings using template patterns.

SELECT to_char(current_date, 'Day, DDth Month YYYY');
-- Returns: 'Wednesday, 04th June 2026'

SELECT to_char(12345.67, '99,999.99');
-- Returns: ' 12,345.67'

⚠️ Performance trap: Never filter with WHERE to_char(date_col, 'YYYY') = '2026'. Use a date range to preserve index efficiency.

Optimization: Apply to_char() only at the final SELECT layer, not in joins or WHERE clauses. For recurring formats, consider a generated column or materialized view.

🛡️

SQL Injection Prevention

SQL injection occurs when untrusted input is concatenated directly into SQL strings, allowing attackers to alter query logic.

Secure Practices:

Parameterized Queries (Prepared Statements): The gold standard.
Input validation: Allow‑list based validation.
Least privilege: Restrict DB user permissions.
Use ORM frameworks: Many auto‑parameterize queries.

-- Vulnerable (never do this)
EXECUTE 'SELECT * FROM users WHERE name = ''' || unsafe_input || '''';

-- Safe with parameterized query
PREPARE safe_user_lookup(text) AS
  SELECT * FROM users WHERE name = $1;
EXECUTE safe_user_lookup('Robert');

Server‑Side Prepared Statements

The PostgreSQL protocol allows parsing and planning a query once, then executing it many times with different parameters – boosting performance and blocking injection.

-- Step 1: PREPARE
PREPURE find_orders_by_status(text) AS
  SELECT id, total FROM orders WHERE status = $1;

-- Step 2: EXECUTE
EXECUTE find_orders_by_status('shipped');

-- Step 3: DEALLOCATE
DEALLOCATE find_orders_by_status;

⚡ Pro tip: Use connection poolers like PgBouncer in transaction mode for maximum benefit. Always deallocate statements in long‑running sessions.

SQL Comments

Both single‑line (--) and multi‑line (/* */) comments help document complex logic.

/*
 * Generate monthly calendar then LEFT JOIN factbook data
 * to ensure every day appears in the result set.
 */
SELECT calendar.day, COALESCE(fact.visits, 0) AS visits
FROM generate_series('2026-01-01', '2026-01-31', '1 day') AS calendar(day)
LEFT JOIN factbook fact ON fact.day = calendar.day;

CAST & Type Conversion

Explicitly convert between data types using CAST() or PostgreSQL shorthand ::.

SELECT CAST('2026-06-04' AS DATE);
SELECT '123'::INTEGER;
SELECT current_date::TEXT;

Avoid casting indexed columns in WHERE clauses – instead cast the input value to match the column type.

COALESCE()

Returns the first non‑null value in a list – ideal for providing fallback defaults.

SELECT COALESCE(bonus, commission, 0) AS total_incentive FROM employees;

Best practice: Place the most likely non‑null value first for short‑circuit efficiency. Always use COALESCE in arithmetic to avoid NULL + 10 = NULL pitfalls.

generate_series()

Set‑returning function that creates a sequence of numbers or timestamps – perfect for filling gaps or generating test data.

-- Generate numbers 1..5
SELECT * FROM generate_series(1, 5);

-- Generate every day in March 2026
SELECT generate_series('2026-03-01'::date, '2026-03-31'::date, '1 day');

📊 Reporting tip: LEFT JOIN generate_series to ensure all dates appear even when no data exists.

@> Contains Operator

Checks if left operand completely contains the right operand – used with JSONB, arrays, ranges, and geometric types.

-- JSONB: does the document contain this key-value pair?
SELECT * FROM logs WHERE data @> '{"level": "ERROR"}';

-- Array: does the array contain both 1 and 2?
SELECT * FROM tags WHERE tag_ids @> ARRAY[1, 2];

🔥 Always create a GIN index on JSONB/array columns when using @>: CREATE INDEX idx_gin ON my_table USING gin(data_column);

jsonb_pretty()

Formats minified JSONB into indented, human‑readable text – invaluable for debugging.

SELECT jsonb_pretty('{"id": 1, "name": "PostgreSQL", "features": ["ACID", "JSONB"]}'::jsonb);

Never use jsonb_pretty() in production API responses – let your application handle formatting to avoid wasting database CPU.

show server_version;

Quick utility command to display the exact PostgreSQL version string.

SHOW server_version;
-- Example output: "16.3 (Debian 16.3-1.pgdg120+1)"

SELECT Clause

Specifies which columns or expressions the query returns.

SELECT first_name, last_name, age + 5 AS future_age FROM users;

⚡ Stop using SELECT * in production: explicit column lists enable index‑only scans and prevent slowdowns when tables add large columns.

FROM Clause

Defines the source tables, views, subqueries, or set‑returning functions that feed the query.

FROM orders JOIN customers ON orders.cust_id = customers.id
FROM (SELECT * FROM active_users) AS sub

Order of execution: FROM is evaluated first, then WHERE, then SELECT.

WHERE Clause

Filters rows before aggregation or sorting. Essential for performance and data safety.

WHERE status = 'active' AND price > 100
WHERE created_at >= '2026-01-01' -- Sargable

Index‑friendly rule: Keep columns "naked". Prefer WHERE date_col >= '2026-01-01' over WHERE EXTRACT(YEAR FROM date_col) = 2026.

ORDER BY Clause

Sorts final output rows. Without it, row order is never guaranteed.

ORDER BY last_name ASC, first_name ASC
ORDER BY created_at DESC NULLS LAST;

Optimization: Create indexes on frequently sorted columns to avoid explicit sorts. Increase work_mem for large in‑memory sorts.

JOIN Clause

Combines rows from multiple tables based on related columns. Prefer explicit JOIN ... ON syntax.

SELECT *
FROM orders
JOIN customers ON orders.customer_id = customers.id
LEFT JOIN payments ON orders.id = payments.order_id;

✅ Always index foreign key columns – it's the #1 cause of slow joins.

USING Keyword

Shorthand for equi‑joins when column names are identical. Removes duplicate columns from output.

SELECT * FROM orders JOIN customers USING (customer_id);
-- vs wordy: ON orders.customer_id = customers.customer_id

Also used in dynamic SQL: EXECUTE ... USING variable; to prevent injection.

COUNT(*)

Returns total number of rows matching the query (including rows with nulls).

SELECT COUNT(*) FROM orders WHERE status = 'completed';

On huge tables, COUNT(*) can be slow. For approximate numbers, query the system catalog: SELECT reltuples::bigint FROM pg_class WHERE relname = 'my_table';

Myth buster: COUNT(1) is not faster than COUNT(*) – they compile to the same plan.

Final Thoughts

Mastering these PostgreSQL commands transforms you from a casual SQL user into a database expert. From session tuning with SET to leveraging generate_series for time‑series reporting, and from preventing SQL injection with prepared statements to optimizing COUNT(*) – each technique is a tool for building robust, high‑performance applications.

Key takeaways:

Use SET LOCAL inside transactions for safe temporary changes.
Format only at display layer – keep filtering and joins index‑friendly.
Parameterize all user input to block SQL injection.
GIN indexes on JSONB columns make @> lightning fast.
Explicit column lists in SELECT enable covering indexes.

← Back to Blog

PostgreSQL Mastery: Essential SQL Commands, Functions & Performance Best Practices

📋 Table of Contents