Searchable SQL reference

Search common PostgreSQL SQL statements, clauses, and database functions.

Use this chapter as a quick reference while practicing. Search by a keyword such as foreign key, group by, commit, or explain; expand an entry for its purpose and a compact PostgreSQL example. The examples use the shop schema introduced earlier.

This is a focused learning index, not an exhaustive SQL grammar. Follow each documentation link for full syntax and version-specific behavior.

Sources: PostgreSQL documentation, GeeksforGeeks SQL Tutorial

28 results in 6 categories

Define tables and rules

Create and change database objects with PostgreSQL DDL.

6
CREATE TABLECREATE TABLE table_name (column type, ...)Create a table with named columns and data types.

Define primary keys, foreign keys, defaults, and constraints as part of the schema.

Example
CREATE TABLE categories (
    category_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL UNIQUE
);
PostgreSQL documentation
ALTER TABLEALTER TABLE table_name actionChange the definition of an existing table.

Use it to add or change a column or constraint. Test migrations against realistic data.

Example
ALTER TABLE products
ADD COLUMN category_id integer REFERENCES categories(category_id);
PostgreSQL documentation
DROP TABLEDROP TABLE table_nameRemove a table definition and its data.

This is destructive DDL. Dependencies and cascade behavior require care; avoid running it on valuable data unintentionally.

Example
DROP TABLE IF EXISTS practice_notes;
PostgreSQL documentation
PRIMARY KEYPRIMARY KEY (column, ...)Identify each row uniquely and disallow null key values.

A table can have one primary-key constraint; a key can contain one or multiple columns.

Example
CREATE TABLE order_items (
    order_id integer,
    product_id integer,
    PRIMARY KEY (order_id, product_id)
);
PostgreSQL documentation
FOREIGN KEYREFERENCES other_table (key)Require a referenced row to exist in another table.

Foreign keys enforce referential integrity. Choose delete/update actions based on the domain rules.

Example
customer_id integer NOT NULL
    REFERENCES customers(customer_id)
PostgreSQL documentation
UNIQUE / CHECKUNIQUE (column) | CHECK (condition)Enforce uniqueness or a Boolean rule on stored values.

Use constraints for rules that should hold no matter which application writes the data.

Example
email text NOT NULL UNIQUE,
price numeric(10, 2) CHECK (price >= 0)
PostgreSQL documentation

Read and change rows

Use CRUD statements and common query clauses.

6
SELECTSELECT columns FROM table WHERE conditionRead rows and choose the output columns.

Use explicit column names in application queries; `*` is handy for exploration but can make a result shape unstable.

Example
SELECT product_id, name, price
FROM products
WHERE active = true
ORDER BY price DESC
LIMIT 10;
PostgreSQL documentation
INSERTINSERT INTO table (columns) VALUES (...)Create new rows.

Name the target columns to make the statement clear and resilient to table column order.

Example
INSERT INTO customers (name, email)
VALUES ('Asha Rao', 'asha@example.test')
RETURNING customer_id;
PostgreSQL documentation
UPDATEUPDATE table SET column = value WHERE conditionChange values in existing rows.

Without a restrictive WHERE clause, every row can be updated. Check affected rows in application code.

Example
UPDATE products
SET active = false
WHERE product_id = 12;
PostgreSQL documentation
DELETEDELETE FROM table WHERE conditionRemove rows from a table.

Without a WHERE clause, every row is a target. Referencing foreign keys may prevent deleting a row.

Example
DELETE FROM order_items
WHERE order_id = 42 AND product_id = 7;
PostgreSQL documentation
WHEREWHERE boolean_conditionFilter rows before grouping and aggregation.

Combine comparisons with AND, OR, and NOT. Use IS NULL rather than equality to test for null.

Example
SELECT name, price
FROM products
WHERE active AND price >= 20;
PostgreSQL documentation
ORDER BYORDER BY expression [ASC | DESC]Sort the result rows.

Add a stable tie-breaker when consumers depend on consistent ordering.

Example
SELECT order_id, ordered_at
FROM orders
ORDER BY ordered_at DESC, order_id DESC;
PostgreSQL documentation

Join and relate data

Combine tables using their key relationships.

3
INNER JOINFROM left_table JOIN right_table ON conditionReturn matching row pairs from both inputs.

Use an explicit ON condition to show how the rows relate.

Example
SELECT o.order_id, c.email
FROM orders AS o
JOIN customers AS c USING (customer_id);
PostgreSQL documentation
LEFT JOINFROM left_table LEFT JOIN right_table ON conditionKeep every left-side row and include matching right-side values.

Unmatched right-side columns are NULL. Conditions on the right-side table in WHERE may remove these rows.

Example
SELECT c.customer_id, o.order_id
FROM customers AS c
LEFT JOIN orders AS o USING (customer_id);
PostgreSQL documentation
USINGJOIN table USING (shared_column)Join tables on identically named columns.

Convenient when both inputs share the join-column name; use ON when the expressions or names differ.

Example
SELECT order_id, email
FROM orders
JOIN customers USING (customer_id);
PostgreSQL documentation

Summarize and compose queries

Aggregate rows, filter groups, and name intermediate query results.

7
COUNTCOUNT(*) | COUNT(expression)Count rows or non-null expression values.

COUNT(*) counts rows in the group; COUNT(column) skips rows where that column is NULL.

Example
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;
PostgreSQL documentation
SUM / AVGSUM(expression) | AVG(expression)Calculate a total or arithmetic mean over input values.

Often used with GROUP BY. Consider numeric types and NULL behavior when choosing the expression.

Example
SELECT order_id, SUM(quantity * unit_price) AS total
FROM order_items
GROUP BY order_id;
PostgreSQL documentation
GROUP BYGROUP BY expression [, ...]Produce one result group for each distinct grouping value.

Selected expressions must be grouped or aggregated.

Example
SELECT status, COUNT(*)
FROM orders
GROUP BY status;
PostgreSQL documentation
HAVINGHAVING aggregate_conditionFilter groups after aggregation.

Use WHERE to filter source rows and HAVING to filter the resulting groups.

Example
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2;
PostgreSQL documentation
WITH (CTE)WITH name AS (query) SELECT ...Give a name to a query result used by one statement.

CTEs can make multi-step SQL easier to read. They do not automatically improve performance.

Example
WITH paid_orders AS (
    SELECT * FROM orders WHERE status = 'paid'
)
SELECT customer_id, COUNT(*)
FROM paid_orders
GROUP BY customer_id;
PostgreSQL documentation
EXISTSWHERE EXISTS (subquery)Test whether a subquery returns at least one row.

Useful for expressing existence checks without returning or counting all matching rows.

Example
SELECT c.customer_id
FROM customers AS c
WHERE EXISTS (
    SELECT 1 FROM orders AS o
    WHERE o.customer_id = c.customer_id
);
PostgreSQL documentation
CREATE VIEWCREATE VIEW view_name AS querySave a query definition under a reusable name.

A normal view is a query interface, not a stored snapshot of its results.

Example
CREATE VIEW paid_orders AS
SELECT order_id, customer_id, ordered_at
FROM orders
WHERE status = 'paid';
PostgreSQL documentation

Transactions and performance

Control multi-step work and investigate query execution.

3
BEGIN / COMMIT / ROLLBACKBEGIN; ... COMMIT; | ROLLBACK;Start a transaction and either commit or undo its changes.

Group related writes so they succeed or fail together. Keep transactions focused.

Example
BEGIN;
UPDATE accounts SET balance = balance - 20 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 20 WHERE account_id = 2;
COMMIT;
PostgreSQL documentation
CREATE INDEXCREATE INDEX index_name ON table (column, ...)Create a data structure that may accelerate some queries.

Indexes add storage and write overhead. Evaluate them against real query patterns and plans.

Example
CREATE INDEX orders_customer_ordered_at_idx
ON orders (customer_id, ordered_at DESC);
PostgreSQL documentation
EXPLAINEXPLAIN [ANALYZE] statementInspect the query planner's execution strategy.

EXPLAIN ANALYZE executes the statement. Use extra caution with data-changing statements.

Example
EXPLAIN
SELECT order_id, ordered_at
FROM orders
WHERE customer_id = 7;
PostgreSQL documentation

Reusable database logic

Find the PostgreSQL statements for routines and triggers.

3
CREATE FUNCTIONCREATE FUNCTION name(args) RETURNS type ...Define a callable routine that returns a value or rows.

PostgreSQL functions can be written in SQL or other supported languages; privileges and volatility properties matter.

Example
CREATE FUNCTION line_total(qty integer, price numeric)
RETURNS numeric LANGUAGE SQL IMMUTABLE
AS $$ SELECT qty * price $$;
PostgreSQL documentation
CREATE PROCEDURECREATE PROCEDURE name(args) ...Define a database routine invoked as an operation.

Procedure capabilities and transaction behavior vary by engine. PostgreSQL procedures are called with CALL.

Example
CALL refresh_monthly_report();
PostgreSQL documentation
CREATE TRIGGERCREATE TRIGGER name event ON table ...Attach automatic behavior to a table event.

A PostgreSQL trigger invokes a trigger function. Keep implicit side effects narrow and observable.

Example
CREATE TRIGGER orders_audit
AFTER INSERT ON orders
FOR EACH ROW EXECUTE FUNCTION audit_order_insert();
PostgreSQL documentation

Finished the course? Visit the references and further reading.