---
title: "PostgreSQL"
lang: "en"
author: "Mohammad Abu Mattar"
canonical: https://mkabumattar.com/postgresql
---

0

[Home](/)›[Cheatsheets](/cheatsheets)

Cheatsheets

# PostgreSQL

PostgreSQL reference guide covering psql commands, database creation, tables, queries, functions, joins, transactions, indexes, and advanced SQL operations.

10 Categories27 Sections100 ExamplesPublished: 28 Feb 2026

[Markdown for AI(opens in a new tab)](/postgresql/index.md "Open the plain-Markdown version of this page, for pasting into an AI tool")

Series

[Databases & Data Persistence](/series/databases--data-persistence)1/2

[NextRedis](/cheatsheets/redis)

All posts in this series (2)

Cheatsheets2

1.  [PostgreSQLYou are here](/cheatsheets/postgresql)
2.  [Redis](/cheatsheets/redis)

PostgreSQL reference guide covering psql commands, database creation, tables, queries, functions, joins, transactions, indexes, and advanced SQL operations.

[Getting Started](#category-getting_started)

-   [psql Connection and Basic Commands](#section-psql_connection)
-   [psql Help and Meta-Commands](#section-psql_help_commands)
-   [Connection Environment Variables](#section-connection_env_variables)

[Database Management](#category-database_management)

-   [CREATE DATABASE](#section-create_database)
-   [ALTER DATABASE](#section-alter_database)
-   [DROP DATABASE](#section-drop_database)

[Table Operations](#category-table_operations)

-   [CREATE TABLE](#section-create_table)
-   [Data Types](#section-data_types)
-   [ALTER TABLE](#section-alter_table)

[INSERT & UPDATE](#category-insert_update_delete)

-   [INSERT Statements](#section-insert_statements)
-   [UPDATE Statements](#section-update_statements)
-   [DELETE Statements](#section-delete_statements)

[SELECT Basics](#category-select_queries)

-   [SELECT and WHERE](#section-select_where)
-   [ORDER BY and LIMIT](#section-order_by_limit)

[JOINs & Subqueries](#category-joins_subqueries)

-   [JOIN Types](#section-join_types)
-   [Subqueries](#section-subqueries)

[Aggregation & Grouping](#category-aggregation_grouping)

-   [Aggregate Functions](#section-aggregate_functions)
-   [GROUP BY and HAVING](#section-group_by_having)

[Advanced Queries](#category-advanced_queries)

-   [Window Functions](#section-window_functions)
-   [Common Table Expressions (CTEs)](#section-cte_common_table_expressions)
-   [Indexes and Views](#section-indexes_views)

[Database Functions](#category-database_functions)

-   [String Functions](#section-string_functions)
-   [Date and Time Functions](#section-date_functions)
-   [Math Functions](#section-math_functions)

[Transactions & Performance](#category-transactions_performance)

-   [Transaction Control](#section-transaction_control)
-   [EXPLAIN and ANALYZE](#section-explain_analyze)
-   [Query Optimization Tips](#section-query_optimization)

No commands found

Try adjusting your search term

## Getting Started

### psql Connection and Basic Commands

Connect to PostgreSQL database server and execute basic commands

#### Accessibility

Beginner

#### Best Practices

-   Use environment variable PGPASSWORD for passwords instead of command line
-   Always specify explicit host and port in scripts
-   Use .pgpass file for persistent credentials with 0600 permissions
-   Never commit passwords in connection strings to version control

#### Common Errors

-   **psql: error: could not translate host name to address: Name or service not known:**
-   **psql: error: FATAL: Ident authentication failed for user:**
-   **psql: error: connection to server at localhost (127.0.0.1), port 5432 failed:**

#### Keywords

psqlconnectionconnection stringhostportdatabase

[Learn more](https://www.postgresql.org/docs/current/app-psql.html)

#### Connect to Local PostgreSQL Server

Connect to PostgreSQL server running on localhost as the postgres user

Code

Terminal window

```
psql -U postgres -h localhost -p 5432
```

Execution

```
1psql (14.2)2Type "help" for help.3postgres=#
```

-   Default port is 5432
-   \-U specifies username
-   \-h specifies hostname
-   \-p specifies port number

#### Connect to Specific Database

Connect directly to a specific database with credentials

Code

Terminal window

```
psql -U username -d database_name -h localhost
```

Execution

```
1psql (14.2)2SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384)3Type "help" for help.4database_name=#
```

-   \-d specifies the database name
-   SSL connection shown if available

#### Connect Using Connection String

Use connection string URI format to connect to database

Code

Terminal window

```
psql "postgresql://user:password@localhost:5432/mydb"
```

Execution

```
1psql (14.2)2Type "help" for help.3mydb=#
```

-   Format is postgresql://\[user\[:password\]@\]\[host\]\[:port\]\[/dbname\]
-   Secure method for credentials

### psql Help and Meta-Commands

Navigate help system and use meta-commands in psql

#### Accessibility

Beginner

#### Best Practices

-   Use \\d when exploring unfamiliar databases
-   Save frequently used queries to .sql files instead of retyping
-   Enable \\timing to monitor query performance
-   Use \\watch to repeatedly execute and monitor queries

#### Common Errors

-   **"\\\\d: invalid command \\\\d (after first line) - Use \\\\? for help.":**
-   **"relation does not exist" when trying to describe non-existent table:**

#### Keywords

helpmeta-commandsbackslash commandsdescribelist

[Learn more](https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-META-COMMANDS)

#### Display All Meta-Commands

Display all available meta-commands and shortcuts

Code

```
1\?
```

Execution

```
1General2  \copyright             show PostgreSQL usage and distribution terms3  \g [FILE] or ;         execute query (and send results to file or |pipe)4  \gset [PREFIX]         execute query and store results in psql variables5  \gx                    as \g, but forces expanded output mode6  \q                     quit psql
```

-   Meta-commands start with backslash
-   Command list is very long, use with pager

#### Get Help on SQL Commands

Show syntax and description for SQL commands

Code

```
1\h SELECT
```

Execution

```
1Command:     SELECT2Description: retrieve rows from a table or view3Syntax:4[ WITH [ RECURSIVE ] with_query [, ...] ]5SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]6    [ * | expression [ [ AS ] output_name ] [, ...] ]
```

-   Works with all SQL keywords
-   Handy for a quick syntax lookup

#### List All Databases

List all databases in the PostgreSQL server

Code

```
1\l
```

Execution

```
1List of databases2Name    |  Owner   | Encoding | Collate | Ctype | Access privileges3-----------+----------+----------+---------+-------+-------------------4postgres  | postgres | UTF8     | C       | C     |5template0 | postgres | UTF8     | C       | C     | =c/postgres6template1 | postgres | UTF8     | C       | C     | =c/postgres7testdb    | postgres | UTF8     | C       | C     |8(4 rows)
```

-   Shows database name, owner, encoding, and privileges
-   Template databases are system templates

#### Describe Table Structure

Show detailed structure of a table including columns and constraints

Code

```
1\d users
```

Execution

```
1Table "public.users"2Column   |       Type        | Collation | Nullable | Default3-----------+-------------------+-----------+----------+---------4id        | integer           |           | not null |5username  | character varying |           | not null |6email     | character varying |           | not null |7created_at| timestamp without |           | not null | now()8Indexes:9    "users_pkey" PRIMARY KEY, btree (id)10    "users_email_key" UNIQUE, btree (email)
```

-   Use \\d+ for extended information with access privileges
-   Shows ALL constraints and indexes

### Connection Environment Variables

Use environment variables to configure PostgreSQL connections

#### Accessibility

Beginner

#### Best Practices

-   Always use .pgpass instead of PGPASSWORD for production
-   Set restrictive file permissions on .pgpass (600)
-   Use different credentials for different environments
-   Never commit .pgpass to version control

#### Common Errors

-   **psql: error: password authentication failed for user:**
-   **.pgpass: 644 is not protected - too open:**

#### Keywords

PGHOSTPGPORTPGUSERPGPASSWORDPGDATABASE

[Learn more](https://www.postgresql.org/docs/current/libpq-envars.html)

#### Set Connection Environment Variables

Set environment variables so psql uses them automatically

Code

Terminal window

```
export PGHOST=localhostexport PGPORT=5432export PGUSER=postgresexport PGDATABASE=mydbpsql
```

Execution

```
1psql (14.2)2Type "help" for help.3mydb=#
```

-   psql reads these variables if not specified on command line
-   Command line arguments override environment variables

#### Using .pgpass for Password Storage

Store credentials in .pgpass file for passwordless connections

Code

Terminal window

```
cat ~/.pgpassecho "localhost:5432:*:postgres:password123" >> ~/.pgpasschmod 0600 ~/.pgpass
```

Execution

```
1localhost:5432:*:postgres:password123
```

-   File should contain lines in format: hostname:port:database:user:password
-   Must have 0600 permissions for security
-   Asterisk (\*) matches any database

## Database Management

### CREATE DATABASE

Create new databases on PostgreSQL server

#### Accessibility

Beginner

#### Best Practices

-   Always specify OWNER explicitly for clarity
-   Use UTF8 encoding for international support
-   Set CONNECTION LIMIT to prevent resource exhaustion
-   Use descriptive database names in snake\_case
-   Create from template0 for clean databases

#### Common Errors

-   **"ERROR: database with OID exists" when the database name is already taken:**
-   **"ERROR: invalid encoding name" when the encoding is misspelled:**

#### Keywords

CREATE DATABASEencodingownertemplatetablespace

[Learn more](https://www.postgresql.org/docs/current/sql-createdatabase.html)

#### Create Simple Database

Create a new empty database with default settings

Code

```
1CREATE DATABASE my_app;
```

Execution

```
1CREATE DATABASE
```

-   Default encoding is UTF8 on modern PostgreSQL
-   Default owner is the current user

#### Create Database with Specifications

Create database with specific owner, encoding, and locale settings

Code

```
1CREATE DATABASE company_db2  OWNER postgres3  ENCODING 'UTF8'4  LOCALE 'en_US.UTF-8'5  TEMPLATE template0;
```

Execution

```
1CREATE DATABASE
```

-   template0 is clean template without any extra objects
-   template1 is the default but may contain custom objects
-   Always specify OWNER for clarity in production

#### Create Database with Connection Limit

Create database with maximum connection limit set

Code

```
1CREATE DATABASE test_db2  CONNECTION LIMIT 50;
```

Execution

```
1CREATE DATABASE
```

-   Prevents resource exhaustion from excessive connections
-   Value of -1 allows unlimited connections

### ALTER DATABASE

Modify database properties and configurations

#### Accessibility

Intermediate

#### Best Practices

-   Disconnect all clients before renaming a database
-   Change ownership to dedicated database user accounts
-   Monitor and adjust CONNECTION LIMIT based on usage patterns

#### Common Errors

-   **"ERROR: database is being accessed by other users" when renaming:**
-   **"ERROR: user or database does not exist":**

#### Keywords

ALTER DATABASERENAMEOWNERSETRESET

[Learn more](https://www.postgresql.org/docs/current/sql-alterdatabase.html)

#### Rename Database

Rename an existing database

Code

```
1ALTER DATABASE old_db RENAME TO new_db;
```

Execution

```
1ALTER DATABASE
```

-   Cannot rename database while connected to it
-   No connections must be active to the database

#### Change Database Owner

Transfer database ownership to different user

Code

```
1ALTER DATABASE my_db OWNER TO new_owner;
```

Execution

```
1ALTER DATABASE
```

-   Current owner or superuser can perform this operation

#### Set Connection Limit

Change the maximum number of concurrent connections

Code

```
1ALTER DATABASE my_db CONNECTION LIMIT 100;
```

Execution

```
1ALTER DATABASE
```

-   Allows adjusting limits without dropping database

### DROP DATABASE

Remove databases from PostgreSQL server

#### Accessibility

Intermediate

#### Best Practices

-   Always backup database before dropping in production
-   Use IF EXISTS for safe idempotent operations
-   Require explicit confirmation before dropping in scripts
-   Never drop production databases without approval process

#### Common Errors

-   **"ERROR: database is being accessed by other users":**
-   **"ERROR: database does not exist" without IF EXISTS:**

#### Keywords

DROP DATABASEIF EXISTSWITHFORCE

[Learn more](https://www.postgresql.org/docs/current/sql-dropdatabase.html)

#### Drop Database

Remove a database and all its objects permanently

Code

```
1DROP DATABASE my_db;
```

Execution

```
1DROP DATABASE
```

-   Operation is irreversible
-   No connections can be active to the database

#### Drop Database If Exists

Drop database only if it exists, no error if it does not

Code

```
1DROP DATABASE IF EXISTS my_db;
```

Execution

```
1DROP DATABASE
```

-   Useful for idempotent scripts
-   Does not error if database doesn't exist

#### Force Drop Database with Active Connections

Forcefully terminate connections and drop database

Code

```
1DROP DATABASE my_db WITH (FORCE);
```

Execution

```
1DROP DATABASE
```

-   Available in PostgreSQL 13+
-   Forcefully disconnects all users before dropping

## Table Operations

### CREATE TABLE

Create tables with columns and constraints

#### Accessibility

Beginner

#### Best Practices

-   Always define PRIMARY KEY for every table
-   Use explicit data types (DECIMAL for money, not FLOAT)
-   Add NOT NULL constraints to required columns
-   Use foreign keys to maintain referential integrity
-   Include created\_at with DEFAULT CURRENT\_TIMESTAMP

#### Common Errors

-   **"ERROR: syntax error at or near PRIMARY KEY":**
-   **"ERROR: relation does not exist" for foreign key reference:**
-   **"ERROR: duplicate key value violates unique constraint":**

#### Keywords

CREATE TABLEPRIMARY KEYFOREIGN KEYUNIQUENOT NULLDEFAULT

[Learn more](https://www.postgresql.org/docs/current/sql-createtable.html)

#### Create Basic Table

Create users table with auto-incrementing primary key and constraints

Code

```
1CREATE TABLE users (2  id SERIAL PRIMARY KEY,3  username VARCHAR(50) NOT NULL,4  email VARCHAR(100) NOT NULL UNIQUE,5  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP6);
```

Execution

```
1CREATE TABLE
```

-   SERIAL automatically creates sequence for id
-   UNIQUE constraint on email prevents duplicates
-   DEFAULT CURRENT\_TIMESTAMP sets creation time automatically

#### Create Table with CHECK Constraint

Create table with CHECK constraints to validate data

Code

```
1CREATE TABLE products (2  id SERIAL PRIMARY KEY,3  name VARCHAR(100) NOT NULL,4  price DECIMAL(10, 2) NOT NULL,5  stock INT DEFAULT 0,6  CHECK (price > 0),7  CHECK (stock >= 0)8);
```

Execution

```
1CREATE TABLE
```

-   CHECK constraint rejects a price of zero or less
-   Multiple CHECK constraints allowed
-   Prevents invalid data at database level

#### Create Table with Foreign Key

Create orders table with foreign key referencing users

Code

```
1CREATE TABLE orders (2  id SERIAL PRIMARY KEY,3  user_id INT NOT NULL,4  order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,5  total DECIMAL(10, 2),6  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE7);
```

Execution

```
1CREATE TABLE
```

-   ON DELETE CASCADE removes orders when user is deleted
-   ON DELETE RESTRICT would prevent deletion if child records exist
-   Foreign key enforces referential integrity

#### Create Table with ENUM Type

Create custom ENUM type and use in table constraint

Code

```
1CREATE TYPE order_status AS ENUM ('pending', 'shipped', 'delivered', 'cancelled');2CREATE TABLE shipments (3  id SERIAL PRIMARY KEY,4  order_id INT NOT NULL,5  status order_status DEFAULT 'pending',6  FOREIGN KEY (order_id) REFERENCES orders(id)7);
```

Execution

```
1CREATE TYPE2CREATE TABLE
```

-   ENUM restricts column to predefined values
-   More efficient than VARCHAR with CHECK
-   Type is reusable across multiple tables

### Data Types

PostgreSQL data types for different column definitions

#### Accessibility

Beginner

#### Best Practices

-   Use DECIMAL for financial data, not FLOAT
-   Use TIMESTAMP WITH TIME ZONE for global applications
-   Use JSONB instead of JSON for better performance
-   Avoid TEXT when VARCHAR with limit is more appropriate

#### Common Errors

-   **"ERROR: numeric field overflow" from SMALLINT overflow:**
-   **"ERROR: date/time field value out of range":**

#### Keywords

INTEGERVARCHARDECIMALTIMESTAMPBOOLEANARRAYJSON

[Learn more](https://www.postgresql.org/docs/current/datatype.html)

#### Numeric Data Types

Create table with various numeric data types

Code

```
1CREATE TABLE numeric_examples (2  small_int SMALLINT,3  regular_int INTEGER,4  big_int BIGINT,5  decimal_value DECIMAL(10, 2),6  float_value FLOAT,7  serial_auto SERIAL8);
```

Execution

```
1CREATE TABLE
```

-   SMALLINT: -32768 to 32767
-   INTEGER: -2147483648 to 2147483647
-   BIGINT: for very large numbers
-   DECIMAL for exact precision (financial data)
-   SERIAL creates auto-incrementing columns

#### String and Character Data Types

Create table with various string data types

Code

```
1CREATE TABLE string_examples (2  char_col CHAR(10),3  varchar_col VARCHAR(255),4  text_col TEXT,5  name_col VARCHAR(100) NOT NULL6);
```

Execution

```
1CREATE TABLE
```

-   CHAR is fixed-length, padded with spaces
-   VARCHAR is variable-length with limit
-   TEXT is variable-length with no limit
-   Use VARCHAR with limit for most cases

#### Date and Time Data Types

Create table with date and time data types

Code

```
1CREATE TABLE datetime_examples (2  date_col DATE,3  time_col TIME,4  timestamp_col TIMESTAMP,5  timestamp_tz TIMESTAMP WITH TIME ZONE6);
```

Execution

```
1CREATE TABLE
```

-   DATE stores only date without time
-   TIME stores only time without date
-   TIMESTAMP stores both date and time
-   TIMESTAMP WITH TIME ZONE includes timezone information

#### JSON and Array Data Types

Create table with JSON and array data types

Code

```
1CREATE TABLE advanced_types (2  json_data JSON,3  jsonb_data JSONB,4  tags TEXT[],5  numbers INTEGER[]6);
```

Execution

```
1CREATE TABLE
```

-   JSONB is binary JSON with better performance
-   JSON stores raw text
-   ARRAY types for storing lists of values
-   Can query array elements directly in SQL

### ALTER TABLE

Modify existing table structures and constraints

#### Accessibility

Intermediate

#### Best Practices

-   Design tables carefully before creation to minimize ALTER operations
-   Add NOT NULL columns only with DEFAULT values
-   Use ALTER TABLE ... SET NOT NULL cautiously on large tables
-   Test ALTER TABLE changes on development database first

#### Common Errors

-   **ERROR: column already exists:**
-   **ERROR: cannot drop column used in function or view:**

#### Keywords

ALTER TABLEADD COLUMNDROP COLUMNRENAMEMODIFY

[Learn more](https://www.postgresql.org/docs/current/sql-altertable.html)

#### Add Column to Existing Table

Add new column to existing table

Code

```
1ALTER TABLE users ADD COLUMN phone VARCHAR(20);
```

Execution

```
1ALTER TABLE
```

-   New column will be NULL for all existing rows
-   Can specify DEFAULT for existing rows

#### Add Column with Default Value

Add column with default value for existing and future rows

Code

```
1ALTER TABLE users2  ADD COLUMN is_active BOOLEAN DEFAULT true;
```

Execution

```
1ALTER TABLE
```

-   DEFAULT applies to new inserts and existing rows

#### Rename Table and Column

Rename both table and column names

Code

```
1ALTER TABLE users RENAME TO customer_users;2ALTER TABLE customer_users RENAME COLUMN phone TO phone_number;
```

Execution

```
1ALTER TABLE2ALTER TABLE
```

-   Rename operations are safe, referential integrity maintained
-   Consider impact on dependent views and applications

## INSERT & UPDATE

### INSERT Statements

Insert data into tables with various methods

#### Accessibility

Beginner

#### Best Practices

-   Use multi-row INSERT for better performance
-   Always specify columns explicitly for clarity
-   Use RETURNING to confirm data was inserted correctly
-   Validate data before insertion
-   Use transactions for multiple related inserts

#### Common Errors

-   **ERROR: duplicate key value violates unique constraint:**
-   **ERROR: null value in column violates not-null constraint:**
-   **ERROR: insert or update on table violates foreign key constraint:**

#### Keywords

INSERT INTOVALUESSELECTDEFAULTRETURNING

[Learn more](https://www.postgresql.org/docs/current/sql-insert.html)

#### Insert Single Row

Insert a single row with specified columns

Code

```
1INSERT INTO users (username, email)2VALUES ('john_doe', 'john@example.com');
```

Execution

```
1INSERT 0 1
```

-   Columns not specified will use DEFAULT or NULL
-   INSERT 0 1 means 1 row inserted with OID 0

#### Insert Multiple Rows

Insert multiple rows in single statement

Code

```
1INSERT INTO users (username, email) VALUES2  ('alice', 'alice@example.com'),3  ('bob', 'bob@example.com'),4  ('charlie', 'charlie@example.com');
```

Execution

```
1INSERT 0 3
```

-   Faster than one INSERT statement per row
-   All rows inserted atomically or none

#### Insert with RETURNING Clause

Insert row and return generated values immediately

Code

```
1INSERT INTO users (username, email)2VALUES ('diana', 'diana@example.com')3RETURNING id, username, email;
```

Execution

```
1id | username |      email2----+----------+------------------342 | diana    | diana@example.com4(1 row)
```

-   RETURNING is PostgreSQL extension
-   Useful for getting auto-generated IDs
-   Can return any columns or expressions

#### Insert from SELECT Query

Insert rows from results of SELECT query

Code

```
1INSERT INTO users_backup (username, email)2SELECT username, email FROM users WHERE created_at > CURRENT_DATE - INTERVAL '30 days';
```

Execution

```
1INSERT 0 15
```

-   Useful for copying or archiving data
-   Can filter and transform data during copy

### UPDATE Statements

Modify existing data in tables

#### Accessibility

Beginner

#### Best Practices

-   Always include WHERE clause to target specific rows
-   Use RETURNING to verify changes were applied
-   Consider adding updated\_at timestamp column
-   Test UPDATE on development database first
-   Use transactions so related rows change together

#### Common Errors

-   **"UPDATE 0" when WHERE condition matches no rows:**
-   **"ERROR:** update or delete on table violates foreign key constraint"

#### Keywords

UPDATESETWHERERETURNINGFROM

[Learn more](https://www.postgresql.org/docs/current/sql-update.html)

#### Update Single Column

Update specific column for matching rows

Code

```
1UPDATE users2SET email = 'newemail@example.com'3WHERE username = 'john_doe';
```

Execution

```
1UPDATE 1
```

-   WHERE clause is critical to avoid updating all rows
-   UPDATE 1 means 1 row was updated

#### Update Multiple Columns

Update multiple columns in single statement

Code

```
1UPDATE users2SET3  email = 'john.doe@example.com',4  is_active = true,5  updated_at = CURRENT_TIMESTAMP6WHERE id = 1;
```

Execution

```
1UPDATE 1
```

-   Can update independent columns simultaneously
-   Use CURRENT\_TIMESTAMP for automatic update tracking

#### Update with Expression

Update columns using expressions referencing current values

Code

```
1UPDATE products2SET price = price * 1.10, updated_at = CURRENT_TIMESTAMP3WHERE category = 'electronics';
```

Execution

```
1UPDATE 15
```

-   price \* 1.10 increases price by 10 percent
-   Updated 15 products with category electronics

#### Update with RETURNING

Update and return the modified row data

Code

```
1UPDATE users2SET last_login = CURRENT_TIMESTAMP3WHERE username = 'alice'4RETURNING id, username, last_login;
```

Execution

```
1id | username |     last_login2----+----------+---------------------32 | alice    | 2026-02-28 14:30:454(1 row)
```

-   Confirms what was actually updated
-   Returns new values after the update

### DELETE Statements

Remove data from tables

#### Accessibility

Beginner

#### Best Practices

-   Always backup table before bulk DELETE operations
-   Use WHERE clause to limit scope
-   Use RETURNING to verify deleted data
-   Prefer TRUNCATE for deleting all rows
-   Use soft deletes (is\_deleted column) for important data

#### Common Errors

-   **"DELETE 0" when WHERE condition matches nothing:**
-   **"ERROR:** update or delete on table violates foreign key constraint"

#### Keywords

DELETEWHERERETURNINGON DELETE CASCADE

[Learn more](https://www.postgresql.org/docs/current/sql-delete.html)

#### Delete Rows with Condition

Delete specific row matching condition

Code

```
1DELETE FROM users WHERE id = 42;
```

Execution

```
1DELETE 1
```

-   DELETE 1 means 1 row was deleted
-   WHERE clause is critical

#### Delete Multiple Rows

Delete old cancelled orders and return their details

Code

```
1DELETE FROM orders2WHERE status = 'cancelled'3AND created_at < CURRENT_DATE - INTERVAL '90 days'4RETURNING id, order_date;
```

Execution

```
1id |  order_date2----+---------------------35 | 2025-11-15 10:20:30412 | 2025-10-20 14:45:205(2 rows)
```

-   Complex WHERE allows deleting old data selectively
-   RETURNING shows what was deleted before deletion

#### Delete All Rows

Delete all rows from table (no WHERE clause)

Code

```
1DELETE FROM log_entries;
```

Execution

```
1DELETE 1000
```

-   Very dangerous operation
-   Use TRUNCATE for faster deletion of all rows

## SELECT Basics

### SELECT and WHERE

Retrieve data with conditions and filtering

#### Accessibility

Beginner

#### Best Practices

-   Always use WHERE to filter data, don't fetch unnecessary rows
-   Create indexes on frequently filtered columns
-   Use IN instead of multiple OR conditions
-   Be careful with data types in comparisons

#### Common Errors

-   **"ERROR: invalid input syntax for type integer" from a type mismatch:**
-   **Returns no results when WHERE is too restrictive:**

#### Keywords

SELECTWHEREANDORNOTINBETWEEN

[Learn more](https://www.postgresql.org/docs/current/sql-select.html)

#### Select All Columns

Retrieve all columns from all rows in users table

Code

```
1SELECT * FROM users;
```

Execution

```
1id | username |        email        | created_at2----+----------+----------------------+---------------------31 | john_doe | john@example.com   | 2026-01-15 10:30:0042 | alice    | alice@example.com  | 2026-01-20 14:25:0053 | bob      | bob@example.com    | 2026-02-01 09:15:006(3 rows)
```

-   Using \* is fine for exploration but avoid in production
-   Specify columns explicitly for performance

#### Select Specific Columns

Select only specific columns from table

Code

```
1SELECT id, username, email FROM users;
```

Execution

```
1id | username |        email2----+----------+----------------------31 | john_doe | john@example.com42 | alice    | alice@example.com53 | bob      | bob@example.com6(3 rows)
```

-   More efficient than SELECT \*
-   Only fetches needed columns

#### SELECT with WHERE Condition

Filter results using WHERE clause

Code

```
1SELECT * FROM users WHERE username = 'alice';
```

Execution

```
1id | username |       email       |     created_at2----+----------+-------------------+---------------------32 | alice    | alice@example.com | 2026-01-20 14:25:004(1 row)
```

-   String values must be in single quotes
-   Uses index on username for faster lookup

#### WHERE with Multiple Conditions

Filter with AND operator for multiple conditions

Code

```
1SELECT * FROM users2WHERE created_at > '2026-01-01'3  AND is_active = true;
```

Execution

```
1id | username |        email       |     created_at2----+----------+--------------------+---------------------31 | john_doe | john@example.com  | 2026-01-15 10:30:0042 | alice    | alice@example.com | 2026-01-20 14:25:005(2 rows)
```

-   Both conditions must be true for row to match
-   Can use OR for alternative conditions

#### WHERE with IN Operator

Filter for rows matching any value in list

Code

```
1SELECT * FROM orders2WHERE status IN ('pending', 'shipped', 'processing');
```

Execution

```
1id | user_id | status     |   order_date2----+---------+------------+---------------------31 |       1 | pending    | 2026-02-20 11:00:0043 |       2 | shipped    | 2026-02-18 15:30:0055 |       3 | processing | 2026-02-25 08:45:006(3 rows)
```

-   IN is more readable than multiple OR conditions
-   Can use subquery with IN operator

#### WHERE with BETWEEN

Filter for values within range (inclusive)

Code

```
1SELECT * FROM products2WHERE price BETWEEN 10.00 AND 100.00;
```

Execution

```
1id | name           | price2----+----------------+--------32 | Keyboard       | 45.9944 | Monitor        | 89.505(2 rows)
```

-   BETWEEN is inclusive of both boundaries
-   Works with numbers, dates, and strings

### ORDER BY and LIMIT

Sort and limit query results

#### Accessibility

Beginner

#### Best Practices

-   Always use ORDER BY when results order matters
-   Use LIMIT to prevent returning huge result sets
-   Create indexes on ORDER BY columns for performance
-   Use OFFSET with LIMIT for pagination
-   Consider keyset pagination for large datasets

#### Common Errors

-   **Forgetting ORDER BY with LIMIT makes results unpredictable:**
-   **LIMIT and OFFSET without ORDER BY gives inconsistent results:**

#### Keywords

ORDER BYASCDESCLIMITOFFSET

[Learn more](https://www.postgresql.org/docs/current/sql-select.html)

#### Order Results Ascending

Sort results by created\_at in ascending order (oldest first)

Code

```
1SELECT username, created_at FROM users2ORDER BY created_at ASC;
```

Execution

```
1username |     created_at2----------+---------------------3john_doe | 2026-01-15 10:30:004alice    | 2026-01-20 14:25:005bob      | 2026-02-01 09:15:006(3 rows)
```

-   ASC is the default, can be omitted
-   Sorts from lowest to highest value

#### Order Results Descending

Sort results by created\_at in descending order (newest first)

Code

```
1SELECT username, created_at FROM users2ORDER BY created_at DESC;
```

Execution

```
1username |     created_at2----------+---------------------3bob      | 2026-02-01 09:15:004alice    | 2026-01-20 14:25:005john_doe | 2026-01-15 10:30:006(3 rows)
```

-   DESC sorts from highest to lowest value
-   Useful for getting recent records

#### Limit Results to First N Rows

Order by price descending and return top 5 most expensive products

Code

```
1SELECT * FROM products2ORDER BY price DESC3LIMIT 5;
```

Execution

```
1id | name               | price2----+--------------------+--------31 | Premium Laptop     |899.9942 | Desktop Computer   |749.9953 | Tablet             |399.9964 | Monitor            | 89.5075 | Keyboard           | 45.998(5 rows)
```

-   LIMIT restricts number of rows returned
-   Improves performance for large result sets

#### Pagination with OFFSET and LIMIT

Skip first 20 rows and return next 10 (pagination)

Code

```
1SELECT * FROM users2ORDER BY id3LIMIT 10 OFFSET 20;
```

Execution

```
1id | username |        email        |     created_at2----+----------+----------------------+---------------------321 | user21   | user21@example.com  | 2026-01-15 10:30:00422 | user22   | user22@example.com  | 2026-01-20 14:25:005(10 rows)
```

-   OFFSET skips specified number of rows
-   Page 3 with page size 10 = LIMIT 10 OFFSET 20
-   Use ORDER BY for consistent pagination

## JOINs & Subqueries

### JOIN Types

Combine data from multiple tables using different join types

#### Accessibility

Intermediate

#### Best Practices

-   Use explicit JOIN keywords (INNER, LEFT, RIGHT) for clarity
-   Always use ON clause with clear join conditions
-   Create indexes on join columns for performance
-   Test complex joins on sample data first
-   Consider using aliases for readability

#### Common Errors

-   **"ERROR: column reference is ambiguous" when a column is not qualified with its table name:**
-   **Missing ON clause in JOIN statement:**
-   **Logic errors from wrong join type selection:**

#### Keywords

INNER JOINLEFT JOINRIGHT JOINFULL OUTER JOINCROSS JOIN

[Learn more](https://www.postgresql.org/docs/current/sql-select.html#SQL-FROM)

#### INNER JOIN Two Tables

Return only rows where both tables have matching records

Code

```
1SELECT users.username, orders.id, orders.total2FROM users3INNER JOIN orders ON users.id = orders.user_id;
```

Execution

```
1username | id | total2----------+----+--------3john_doe |  1 |100.004alice    |  2 | 50.005bob      |  3 | 75.006(3 rows)
```

-   Only returns matching rows from both tables
-   ON clause specifies the join condition
-   INNER is the default join type

#### LEFT JOIN Preserving All Left Rows

Include all users even if they have no orders

Code

```
1SELECT users.username, COUNT(orders.id) as order_count2FROM users3LEFT JOIN orders ON users.id = orders.user_id4GROUP BY users.id, users.username;
```

Execution

```
1username | order_count2----------+-------------3john_doe |           24alice    |           15bob      |           06(3 rows)
```

-   bob appears with 0 orders because of LEFT JOIN
-   INNER JOIN would omit users with no orders
-   Useful for finding missing related records

#### RIGHT JOIN

Include all orders even if user was deleted

Code

```
1SELECT users.username, orders.id2FROM users3RIGHT JOIN orders ON users.id = orders.user_id;
```

Execution

```
1username | id2----------+----3john_doe |  14alice    |  25bob      |  36         |  47(4 rows)
```

-   Shows order 4 with NULL username (orphaned order)
-   Opposite of LEFT JOIN

#### FULL OUTER JOIN

Include rows from both tables even if no match

Code

```
1SELECT users.username, orders.id2FROM users3FULL OUTER JOIN orders ON users.id = orders.user_id;
```

Execution

```
1username | id2----------+----3john_doe |  14alice    |  25bob      |  36         |  47(4 rows)
```

-   Combines LEFT and RIGHT JOIN behavior
-   Shows unmatched rows from both sides as NULL

#### Join with Multiple Tables

Chain multiple JOINs to combine data from 4 tables

Code

```
1SELECT users.username, orders.id, products.name2FROM users3INNER JOIN orders ON users.id = orders.user_id4INNER JOIN order_items ON orders.id = order_items.order_id5INNER JOIN products ON order_items.product_id = products.id;
```

Execution

```
1username | id | name2----------+----+-----3john_doe |  1 | Laptop4john_doe |  1 | Mouse5alice    |  2 | Keyboard6(3 rows)
```

-   Each JOIN adds more conditions
-   Order of JOINs can affect performance

### Subqueries

Use queries within queries for complex data retrieval

#### Accessibility

Intermediate

#### Best Practices

-   Prefer JOINs over subqueries when possible for performance
-   Use CTEs (WITH) instead of nested subqueries for readability
-   Check that the subquery returns the expected number of rows
-   Avoid correlated subqueries in SELECT clause

#### Common Errors

-   **"ERROR: subquery returned more than one row":**
-   **"ERROR: column does not exist" from wrong alias:**

#### Keywords

subquerynested queryWHERE subqueryFROM subqueryscalar subquery

[Learn more](https://www.postgresql.org/docs/current/sql-syntax.html#SQL-SYNTAX-LEXICAL)

#### Subquery in WHERE Clause

Find users who placed orders totaling more than 100

Code

```
1SELECT username, email FROM users2WHERE id IN (SELECT user_id FROM orders WHERE total > 100);
```

Execution

```
1username |        email2----------+----------------------3john_doe | john@example.com4alice    | alice@example.com5(2 rows)
```

-   Subquery returns list of user\_ids for IN clause
-   Subquery executes first, result used by outer query

#### Subquery in FROM Clause

Treat subquery result as a table in FROM clause

Code

```
1SELECT avg_order FROM (2  SELECT AVG(total) as avg_order FROM orders3) as order_stats;
```

Execution

```
1avg_order2-----------375.004(1 row)
```

-   Subquery must have alias (order\_stats)
-   Useful for complex aggregations

#### Scalar Subquery in SELECT

Use subquery in SELECT to get count for each user

Code

```
1SELECT username,2  (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) as order_count3FROM users;
```

Execution

```
1username | order_count2----------+-------------3john_doe |           24alice    |           15bob      |           06(3 rows)
```

-   Scalar subquery must return single value per row
-   Uses correlated subquery (references outer table)
-   Can be performance intensive on large datasets

## Aggregation & Grouping

### Aggregate Functions

Use functions that operate on multiple rows

#### Accessibility

Intermediate

#### Best Practices

-   Account for how each aggregate treats NULL values
-   Use DISTINCT within aggregates when needed
-   Create indexes on columns used in aggregates
-   Be cautious with AVG on large datasets with outliers

#### Common Errors

-   **ERROR: column must appear in GROUP BY clause or be aggregate:**
-   **Wrong data type passed to aggregation function:**

#### Keywords

COUNTSUMAVGMINMAXGROUP\_CONCAT

[Learn more](https://www.postgresql.org/docs/current/functions-aggregate.html)

#### COUNT Aggregation

Count total number of rows in table

Code

```
1SELECT COUNT(*) as total_users FROM users;
```

Execution

```
1total_users2-------------3          34(1 row)
```

-   COUNT(\*) counts all rows including NULLs
-   COUNT(column) counts non-NULL values in column

#### SUM Aggregation

Sum all order totals to get total revenue

Code

```
1SELECT SUM(total) as total_revenue FROM orders;
```

Execution

```
1total_revenue2---------------3      225.004(1 row)
```

-   SUM ignores NULL values
-   Returns NULL if no rows selected

#### Average and Min/Max

Calculate average, minimum, and maximum product prices

Code

```
1SELECT2  AVG(price) as avg_price,3  MIN(price) as min_price,4  MAX(price) as max_price5FROM products;
```

Execution

```
1avg_price | min_price | max_price2-----------+-----------+-----------3    267.37 |      9.99 |    899.994(1 row)
```

-   AVG ignores NULL values
-   MIN and MAX work with any comparable data type

#### Multiple Aggregations

Calculate multiple aggregate metrics

Code

```
1SELECT2  COUNT(*) as order_count,3  COUNT(DISTINCT user_id) as unique_users,4  SUM(total) as total_sales,5  AVG(total) as avg_order_value6FROM orders;
```

Execution

```
1order_count | unique_users | total_sales | avg_order_value2-------------+--------------+-------------+-----------------3          8 |            3 |      600.00 |          75.004(1 row)
```

-   DISTINCT counts each value once
-   One query can compute several aggregates in a single pass

### GROUP BY and HAVING

Group rows and filter aggregated results

#### Accessibility

Intermediate

#### Best Practices

-   All non-aggregated columns must be in GROUP BY
-   Use WHERE for row filtering before GROUP BY
-   Use HAVING for aggregate filtering after GROUP BY
-   Create indexes on GROUP BY columns
-   Remember aggregate functions return single value per group

#### Common Errors

-   **ERROR: column must appear in GROUP BY clause or be aggregate:**
-   **Using WHERE on aggregate functions (use HAVING instead):**

#### Keywords

GROUP BYHAVINGaggregate functionsgrouping sets

[Learn more](https://www.postgresql.org/docs/current/sql-select.html#SQL-GROUPBY)

#### GROUP BY Single Column

Group orders by user and calculate stats per user

Code

```
1SELECT user_id, COUNT(*) as order_count, SUM(total) as user_total2FROM orders3GROUP BY user_id;
```

Execution

```
1user_id | order_count | user_total2---------+-------------+------------3      1 |           2 |     150.004      2 |           1 |      50.005      3 |           3 |     200.006(3 rows)
```

-   GROUP BY organizes rows into groups
-   Aggregates calculate values for each group

#### GROUP BY Multiple Columns

Group by date and status to see distribution

Code

```
1SELECT DATE(order_date) as order_day, status, COUNT(*) as count2FROM orders3GROUP BY DATE(order_date), status;
```

Execution

```
1order_day  | status | count2-----------+---------+-------32026-02-20 | pending |     242026-02-20 | shipped |     152026-02-25 | delivered |   16(3 rows)
```

-   Results grouped by both columns in GROUP BY
-   Multiple grouping columns refine the grouping

#### HAVING Clause Filter

Find users with more than 1 order using HAVING

Code

```
1SELECT user_id, COUNT(*) as order_count2FROM orders3GROUP BY user_id4HAVING COUNT(*) > 1;
```

Execution

```
1user_id | order_count2---------+-------------3      1 |           24      3 |           35(2 rows)
```

-   HAVING filters on aggregate values (use after GROUP BY)
-   WHERE filters on individual rows (use before GROUP BY)
-   User 2 excluded because they have only 1 order

#### WHERE and HAVING Together

Filter active products, group by category, show high-value categories

Code

```
1SELECT category, SUM(price) as total_price, COUNT(*) as item_count2FROM products3WHERE status = 'active'4GROUP BY category5HAVING SUM(price) > 500;
```

Execution

```
1category  | total_price | item_count2----------+-------------+------------3electronics|    1250.00 |          54furniture |     750.00 |          35(2 rows)
```

-   WHERE applies before grouping (filters rows)
-   HAVING applies after grouping (filters groups)

## Advanced Queries

### Window Functions

Perform calculations across rows without grouping

#### Accessibility

Advanced

#### Best Practices

-   Use window functions to avoid correlated subqueries
-   Create indexes on columns in ORDER BY of window function
-   Use PARTITION BY where it applies, since it narrows each window
-   Test window functions with small datasets first

#### Common Errors

-   **Forgetting OVER clause in window function:**
-   **Trying to use window functions in WHERE clause:**

#### Keywords

OVERROW\_NUMBERRANKDENSE\_RANKLAGLEADwindow partition

[Learn more](https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-PRECEDENCE)

#### ROW\_NUMBER for Ranking

Assign unique rank to each row based on order total

Code

```
1SELECT2  username,3  order_total,4  ROW_NUMBER() OVER (ORDER BY order_total DESC) as rank5FROM user_orders;
```

Execution

```
1username | order_total | rank2----------+-------------+------3alice    |      300.00 |    14bob      |      250.00 |    25john_doe |      200.00 |    36(3 rows)
```

-   ROW\_NUMBER always gives unique sequential numbers
-   Rows with same value get different row numbers

#### RANK with Ties

Rank with tied rows sharing same rank

Code

```
1SELECT2  username,3  salary,4  RANK() OVER (ORDER BY salary DESC) as salary_rank5FROM employees;
```

Execution

```
1username | salary | salary_rank2----------+--------+-------------3alice    |  80000 |           14bob      |  80000 |           15charlie  |  75000 |           36(3 rows)
```

-   RANK skips numbers after ties (1, 1, 3)
-   DENSE\_RANK does not skip (1, 1, 2)

#### Partition Over Window

Calculate average salary per department as window function

Code

```
1SELECT2  department,3  username,4  salary,5  AVG(salary) OVER (PARTITION BY department) as avg_dept_salary6FROM employees;
```

Execution

```
1department | username | salary | avg_dept_salary2-----------+----------+--------+-----------------3sales      | alice    |  80000 |           750004sales      | bob      |  70000 |           750005it         | charlie  |  90000 |           900006(3 rows)
```

-   PARTITION BY divides rows into separate windows
-   Aggregate calculated within each partition

#### LAG and LEAD for Sequential Access

Get previous and next order amounts for each order

Code

```
1SELECT2  order_date,3  total,4  LAG(total) OVER (ORDER BY order_date) as prev_order,5  LEAD(total) OVER (ORDER BY order_date) as next_order6FROM orders;
```

Execution

```
1order_date  | total | prev_order | next_order2-----------+-------+------------+----------32026-02-20 |100.00 |            |     150.0042026-02-22 |150.00|     100.00 |      75.0052026-02-25 | 75.00|     150.00 |6(3 rows)
```

-   LAG accesses previous row value
-   LEAD accesses next row value
-   NULL for first/last rows without previous/next

### Common Table Expressions (CTEs)

Use WITH clause to define temporary result sets

#### Accessibility

Advanced

#### Best Practices

-   Use CTEs for readability instead of deeply nested subqueries
-   Name CTEs descriptively
-   Recursive CTEs need clear termination conditions
-   Test recursive CTEs with limit to avoid infinite loops

#### Common Errors

-   **Recursive CTE without termination condition:**
-   **Referencing undefined CTE:**

#### Keywords

WITHCTErecursivetemporary result

[Learn more](https://www.postgresql.org/docs/current/queries-with.html)

#### Simple CTE

Use CTE to define high-value orders, then find their customers

Code

```
1WITH high_value_orders AS (2  SELECT * FROM orders WHERE total > 1003)4SELECT username, email FROM users5WHERE id IN (SELECT user_id FROM high_value_orders);
```

Execution

```
1username |        email2----------+----------------------3john_doe | john@example.com4alice    | alice@example.com5(2 rows)
```

-   CTE defined in WITH clause, then used in main query
-   More readable than nested subqueries
-   CTE scoped to single query only

#### Multiple CTEs

Define multiple CTEs and use them together

Code

```
1WITH user_order_counts AS (2  SELECT user_id, COUNT(*) as order_count FROM orders GROUP BY user_id3),4active_users AS (5  SELECT id FROM users WHERE is_active = true6)7SELECT u.id, u.username, oc.order_count8FROM active_users u9JOIN user_order_counts oc ON u.id = oc.user_id;
```

Execution

```
1id | username | order_count2----+----------+-------------3  1 | john_doe |           24  2 | alice    |           15(2 rows)
```

-   Each CTE separated by comma
-   CTEs reference each other

#### Recursive CTE

Use recursive CTE to generate sequence of numbers

Code

```
1WITH RECURSIVE numbers AS (2  SELECT 1 as n3  UNION ALL4  SELECT n + 1 FROM numbers WHERE n < 55)6SELECT * FROM numbers;
```

Execution

```
1n2---3  14  25  36  47  58(5 rows)
```

-   Recursive CTE has initial query and recursive part
-   Useful for hierarchical or tree data
-   Must have termination condition to prevent infinite loop

### Indexes and Views

Create indexes for performance and views for convenience

#### Accessibility

Advanced

#### Best Practices

-   Create indexes on columns used in WHERE and JOIN conditions
-   Avoid over-indexing (indexes slow down writes)
-   Use EXPLAIN ANALYZE to verify indexes help
-   Create views for commonly used complex queries
-   Document views with comments

#### Common Errors

-   **"ERROR: relation already exists" when creating duplicate index:**
-   **Creating too many indexes slowing down writes:**

#### Keywords

CREATE INDEXCREATE VIEWindex typesmaterialized view

[Learn more](https://www.postgresql.org/docs/current/sql-createindex.html)

#### Create Index on Column

Create index on email column for faster lookups

Code

```
1CREATE INDEX idx_users_email ON users(email);
```

Execution

```
1CREATE INDEX
```

-   Speeds up SELECT lookups on the indexed column
-   Increases INSERT/UPDATE time slightly
-   Should be created on frequently queried columns

#### Create Composite Index

Create index on multiple columns for complex queries

Code

```
1CREATE INDEX idx_orders_user_date ON orders(user_id, order_date DESC);
```

Execution

```
1CREATE INDEX
```

-   Useful for queries filtering and sorting by these columns
-   Column order matters for query optimization

#### Create View

Create view for user order statistics

Code

```
1CREATE VIEW user_order_summary AS2SELECT3  u.id, u.username, COUNT(o.id) as order_count, SUM(o.total) as total_spent4FROM users u5LEFT JOIN orders o ON u.id = o.user_id6GROUP BY u.id, u.username;
```

Execution

```
1CREATE VIEW
```

-   View acts like a table but contains query logic
-   SELECT from view returns dynamically calculated results
-   Useful for common complex queries

#### Query a View

Query the view like a regular table

Code

```
1SELECT username, order_count, total_spent2FROM user_order_summary3WHERE order_count > 2;
```

Execution

```
1username | order_count | total_spent2----------+-------------+-------------3john_doe |           3 |      400.004(1 row)
```

-   Views simplify complex queries
-   Can be easier to maintain than repeating complex SQL

## Database Functions

### String Functions

Manipulate and analyze text strings

#### Accessibility

Intermediate

#### Best Practices

-   Use LOWER for case-insensitive comparisons instead of UPPER
-   Handle NULL returns from string functions
-   Use TRIM to clean user input
-   Consider encoding issues with international characters

#### Common Errors

-   **Unexpected NULL returned from CONCAT with NULL values:**
-   **Case sensitivity issues with comparisons:**

#### Keywords

CONCATSUBSTRINGUPPERLOWERLENGTHTRIMREPLACESPLIT\_PART

[Learn more](https://www.postgresql.org/docs/current/functions-string.html)

#### String Concatenation

Combine multiple string columns into single value

Code

```
1SELECT2  CONCAT(first_name, ' ', last_name) as full_name,3  CONCAT(username, '@example.com') as email4FROM users;
```

Execution

```
1full_name  |          email2-----------+------------------------3John Doe   | john_doe@example.com4Alice Smith| alice@example.com5(2 rows)
```

-   CONCAT returns NULL if any argument is NULL
-   Alternative: use || operator

#### Substring Extraction

Extract portion of string using SUBSTRING function

Code

```
1SELECT2  username,3  SUBSTRING(email FROM 1 FOR POSITION('@' IN email) - 1) as email_prefix4FROM users;
```

Execution

```
1username | email_prefix2----------+---------------3john_doe | john4alice    | alice5(2 rows)
```

-   SUBSTRING(string FROM start FOR length)
-   Useful for parsing data from formatted strings

#### Case Conversion

Convert strings to uppercase and lowercase

Code

```
1SELECT2  username,3  UPPER(username) as username_upper,4  LOWER(email) as email_lower5FROM users;
```

Execution

```
1username | username_upper | email_lower2----------+----------------+--------------------3John_Doe | JOHN_DOE       | john@example.com4alice    | ALICE          | alice@example.com5(2 rows)
```

-   UPPER converts to uppercase
-   LOWER converts to lowercase
-   Useful for case-insensitive comparisons

#### String Length and Trimming

Get string length and remove leading/trailing whitespace

Code

```
1SELECT2  username,3  LENGTH(username) as username_length,4  TRIM(notes) as cleaned_notes5FROM users;
```

Execution

```
1username | username_length | cleaned_notes2----------+-----------------+---------------3john_doe |               8 | clean note4alice    |               5 | another note5(2 rows)
```

-   LENGTH returns character count
-   TRIM removes spaces, LTRIM from left, RTRIM from right

#### String Replacement

Replace occurrences of string within text

Code

```
1SELECT2  name,3  REPLACE(description, 'old_text', 'new_text') as updated_description4FROM products;
```

Execution

```
1name    | updated_description2--------+---------------------3Laptop | new_text here4(1 rows)
```

-   REPLACE(string, from, to) replaces all occurrences
-   Case-sensitive

### Date and Time Functions

Work with dates and timestamps

#### Accessibility

Intermediate

#### Best Practices

-   Always use TIMESTAMP WITH TIME ZONE for global applications
-   Use CURRENT\_TIMESTAMP for consistency across queries
-   Use DATE\_TRUNC for time-based grouping
-   Remember to handle timezone awareness
-   Test date calculations with edge cases (leap years, DST)

#### Common Errors

-   **Type mismatches between different date types:**
-   **Timezone issues with TIMESTAMP comparisons:**
-   **Incorrect INTERVAL format:**

#### Keywords

CURRENT\_DATECURRENT\_TIMESTAMPEXTRACTDATE\_TRUNCINTERVALAGE

[Learn more](https://www.postgresql.org/docs/current/functions-datetime.html)

#### Current Date and Time

Get current date and timestamp

Code

```
1SELECT2  CURRENT_DATE as today,3  CURRENT_TIMESTAMP as now,4  NOW() as also_now;
```

Execution

```
1today      |            now             |           also_now2-----------+----------------------------+----------------------------32026-02-28 | 2026-02-28 14:30:45.123456 | 2026-02-28 14:30:45.1234564(1 row)
```

-   CURRENT\_DATE returns date without time
-   CURRENT\_TIMESTAMP returns timestamp with timezone
-   NOW() is alias for CURRENT\_TIMESTAMP

#### Extract Date Components

Extract individual components from date/timestamp

Code

```
1SELECT2  created_at,3  EXTRACT(YEAR FROM created_at) as year,4  EXTRACT(MONTH FROM created_at) as month,5  EXTRACT(DAY FROM created_at) as day6FROM users;
```

Execution

```
1created_at  | year | month | day2-----------+------+-------+-----32026-01-15 | 2026 |     1 |  1542026-02-28 | 2026 |     2 |  285(2 rows)
```

-   EXTRACT returns numeric values for date parts
-   Can use with YEAR, MONTH, DAY, HOUR, MINUTE, SECOND

#### Date Arithmetic with INTERVAL

Add and subtract intervals from dates

Code

```
1SELECT2  order_date,3  order_date + INTERVAL '7 days' as expected_delivery,4  CURRENT_DATE - INTERVAL '30 days' as thirty_days_ago5FROM orders;
```

Execution

```
1order_date | expected_delivery | thirty_days_ago2-----------+------------------+------------------32026-02-20 |      2026-02-27  |     2025-12-2942026-02-25 |      2026-03-04  |     2025-12-295(2 rows)
```

-   INTERVAL specifies duration (days, hours, months, years)
-   Result type depends on operand types (date + interval = date)

#### Calculate Age Between Dates

Calculate age/duration between two timestamps

Code

```
1SELECT2  username,3  created_at,4  AGE(CURRENT_TIMESTAMP, created_at) as account_age5FROM users;
```

Execution

```
1username | created_at | account_age2----------+------------+-------------------------------3john_doe | 2026-01-15 | 1 mon 13 days 04:30:45.1234564alice    | 2026-01-20 | 1 mon 08 days 00:25:30.6543215(2 rows)
```

-   AGE returns interval showing years, months, days, etc
-   Useful for finding how old accounts or events are

#### Date Truncation

Truncate timestamp to specified unit

Code

```
1SELECT2  order_date,3  DATE_TRUNC('day', order_date) as day_start,4  DATE_TRUNC('month', order_date) as month_start,5  DATE_TRUNC('year', order_date) as year_start6FROM orders;
```

Execution

```
1order_date  | day_start   | month_start | year_start2-----------+------------+------------+----------32026-02-20 | 2026-02-20 | 2026-02-01 | 2026-01-014(1 rows)
```

-   Useful for grouping by date parts
-   Can truncate to hour, day, month, year, etc

### Math Functions

Perform mathematical calculations

#### Accessibility

Beginner

#### Best Practices

-   Use ROUND with appropriate decimal places for currency
-   Remember NULL propagation in math operations
-   Use CEIL for stock calculations
-   Consider performance impact of complex math functions

#### Common Errors

-   **Division by zero errors:**
-   **Type mismatches in math operations:**

#### Keywords

ABSROUNDCEILFLOORPOWERSQRTMOD

[Learn more](https://www.postgresql.org/docs/current/functions-math.html)

#### Basic Math Operations

Use various math functions on price values

Code

```
1SELECT2  price,3  ABS(price - 100) as distance_from_100,4  ROUND(price, 2) as rounded_price,5  FLOOR(price) as floor_price,6  CEIL(price) as ceil_price7FROM products;
```

Execution

```
1price | distance_from_100 | rounded_price | floor_price | ceil_price2-------+-------------------+---------------+-------------+----------389.50 |              10.50|         89.50 |          89 |         904(1 row)
```

-   ABS returns absolute value
-   ROUND(number, decimals) rounds to specified places
-   FLOOR rounds down, CEIL rounds up

#### Power and Square Root

Calculate power and square root of values

Code

```
1SELECT2  quantity,3  POWER(quantity, 2) as quantity_squared,4  SQRT(quantity) as square_root5FROM inventory;
```

Execution

```
1quantity | quantity_squared | square_root2----------+------------------+-------------3      10 |              100 |     3.162284(1 row)
```

-   POWER(base, exponent) raises to power
-   SQRT returns square root

#### Modulo Operation

Find remainder of division and filter even IDs

Code

```
1SELECT2  id,3  MOD(id, 10) as remainder_of_div_104FROM users5WHERE MOD(id, 2) = 0;
```

Execution

```
1id | remainder_of_div_102----+-------------------3  2 |                   24  4 |                   45  6 |                   66(3 rows)
```

-   MOD(a, b) returns remainder of a/b
-   MOD(id, 2) = 0 finds even numbers

## Transactions & Performance

### Transaction Control

Use transactions for data consistency

#### Accessibility

Intermediate

#### Best Practices

-   Keep transactions as short as possible
-   Avoid long-running transactions that lock resources
-   Use explicit transaction boundaries for related operations
-   Handle transaction rollback in application code
-   Use savepoints for complex multi-step operations

#### Common Errors

-   **Forgetting to COMMIT, leaving transaction open:**
-   **Deadlocks from conflicting transactions:**
-   **Implicit transaction errors affecting other queries:**

#### Keywords

BEGINCOMMITROLLBACKSAVEPOINTACID

[Learn more](https://www.postgresql.org/docs/current/tutorial-transactions.html)

#### Basic Transaction

Transfer 100 units from user 1 to user 2 atomically

Code

```
1BEGIN;2UPDATE users SET balance = balance - 100 WHERE id = 1;3UPDATE users SET balance = balance + 100 WHERE id = 2;4COMMIT;
```

Execution

```
1BEGIN2UPDATE 13UPDATE 14COMMIT
```

-   Both updates succeed or both fail
-   The two balances never disagree part-way through

#### Transaction Rollback

Start transaction, make changes, then rollback

Code

```
1BEGIN;2UPDATE products SET stock = stock - 10 WHERE id = 5;3SELECT stock FROM products WHERE id = 5;4ROLLBACK;
```

Execution

```
1BEGIN2UPDATE 13 stock4-------5    256(1 row)7ROLLBACK
```

-   ROLLBACK discards all changes in transaction
-   Stock reverts to previous value after rollback

#### Savepoint for Partial Rollback

Use SAVEPOINT to partially rollback failed operations

Code

```
1BEGIN;2INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com');3SAVEPOINT sp1;4INSERT INTO users (username, email) VALUES ('bob', NULL);5ROLLBACK TO sp1;6INSERT INTO users (username, email) VALUES ('charlie', 'charlie@example.com');7COMMIT;
```

Execution

```
1BEGIN2INSERT 0 13SAVEPOINT4ERROR: null value in column violates not-null constraint5ROLLBACK6INSERT 0 17COMMIT
```

-   Savepoint allows rolling back to previous point
-   Alice and Charlie inserted, Bob's insert rolled back

### EXPLAIN and ANALYZE

Analyze query performance and execution plans

#### Accessibility

Advanced

#### Best Practices

-   Use EXPLAIN ANALYZE to verify index effectiveness
-   Look for Seq Scan on large tables (should use index)
-   Check if estimated rows match actual rows
-   Identify slow operations (high cost nodes)
-   Use ANALYZE to gather table statistics

#### Common Errors

-   **Misinterpreting cost as actual time (it's relative):**
-   **Not understanding why index is not being used:**

#### Keywords

EXPLAINANALYZEexecution planquery optimizationindex usage

[Learn more](https://www.postgresql.org/docs/current/sql-explain.html)

#### Basic EXPLAIN Plan

Show query execution plan without actually running query

Code

```
1EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';
```

Execution

```
1Seq Scan on users (cost=0.00..35.50 rows=1 width=200)2  Filter: (email = 'john@example.com'::text)3Planning Time: 0.085 ms
```

-   Seq Scan means full table scan (no index used)
-   Cost is relative unit of query expense
-   Rows indicates estimated number of returned rows

#### EXPLAIN with ANALYZE

Run query and show actual execution statistics

Code

```
1EXPLAIN ANALYZE SELECT u.username, COUNT(o.id)2FROM users u3LEFT JOIN orders o ON u.id = o.user_id4GROUP BY u.id, u.username;
```

Execution

```
1GroupAggregate (cost=100.25..108.50 rows=3 width=20) (actual time=1.235..1.250 rows=3 loops=1)2  -> Sort (cost=100.25..100.50 rows=3 width=20) (actual time=0.845..0.850 rows=3 loops=1)3        Sort Key: u.id4        -> Hash Left Join (cost=35.50..100.00 rows=100 width=20) (actual time=0.250..0.500 rows=100 loops=1)5Planning Time: 0.125 ms6Execution Time: 1.350 ms
```

-   actual time shows real execution time
-   actual rows shows actual returned rows vs estimated
-   Useful for query optimization

#### EXPLAIN with VERBOSE and COSTS

Show detailed execution plan with output columns

Code

```
1EXPLAIN (ANALYZE, VERBOSE, COSTS)2SELECT * FROM products WHERE price BETWEEN 10 AND 100;
```

Execution

```
1Seq Scan on public.products (cost=0.00..35.50 rows=150 width=200)2  Output: id, name, price, stock3  Filter: ((price >= '10.00'::numeric) AND (price <= '100.00'::numeric))4  Planning Time: 0.050 ms5  Execution Time: 0.500 ms
```

-   VERBOSE shows all output columns and filters
-   Helps understand exactly what query is doing

### Query Optimization Tips

Optimize queries for better performance

#### Accessibility

Advanced

#### Best Practices

-   Index frequently searched columns
-   Avoid indexing low-cardinality columns
-   Use EXPLAIN ANALYZE to verify optimization
-   Regularly ANALYZE tables for statistics
-   Consider denormalization for read-heavy workloads
-   Use partitioning for very large tables

#### Common Errors

-   **Creating unnecessary indexes slowing down writes:**
-   **Not analyzing tables after bulk operations:**
-   **Indexing in wrong column order for queries:**

#### Keywords

indexing strategyquery optimizationprepared statementsquery rewritingstatistics

[Learn more](https://www.postgresql.org/docs/current/performance-tips.html)

#### Index for Common Queries

Create compound index on commonly filtered columns

Code

```
1CREATE INDEX idx_users_status_created ON users(status, created_at DESC);2SELECT * FROM users WHERE status = 'active' ORDER BY created_at DESC LIMIT 10;
```

Execution

```
1CREATE INDEX2 id | username | status | created_at3----+----------+--------+---------------------------4  5 | alice    | active | 2026-02-28 14:30:005  3 | bob      | active | 2026-02-27 10:15:006(2 rows)
```

-   Index on (status, created\_at) speeds up query
-   Column order in index matters
-   DESC in index aids ORDER BY DESC queries

#### Use VACUUM to Maintain Performance

Reclaim space and update query planner statistics

Code

Terminal window

```
VACUUM ANALYZE users;VACUUM FULL;
```

Execution

```
1VACUUM2VACUUM
```

-   VACUUM removes dead rows
-   ANALYZE updates optimizer statistics
-   VACUUM FULL locks table (use during maintenance)

#### Batch Large Updates

Update in batches to reduce lock contention

Code

```
1UPDATE products2SET stock = stock - 13WHERE id IN (SELECT product_id FROM orders WHERE status = 'shipped' LIMIT 1000);
```

Execution

```
1UPDATE 950
```

-   LIMIT prevents huge updates holding locks too long
-   Run multiple times to process all records

#### Prepared Statements

Prepare and reuse statements to prevent reparsing

Code

Terminal window

```
PREPARE get_user (INT) AS SELECT * FROM users WHERE id = $1;EXECUTE get_user(1);EXECUTE get_user(2);DEALLOCATE get_user;
```

Execution

```
1PREPARE2 id | username |         email       | created_at3----+----------+--------------------+---------------------4  1 | john_doe | john@example.com  | 2026-01-15 10:30:005(1 row)6PREPARE7DEALLOCATE
```

-   Prepared statements improve performance for repeated queries
-   Also prevent SQL injection attacks

Was this useful?

## Share

[Facebook](https://facebook.com/sharer/sharer.php?u=https%3A%2F%2Fmkabumattar.com%2Fcheatsheets%2Fpostgresql "Share on Facebook")[Twitter](https://twitter.com/intent/tweet/?text=PostgreSQL&url=https%3A%2F%2Fmkabumattar.com%2Fcheatsheets%2Fpostgresql "Share on Twitter")[LinkedIn](https://www.linkedin.com/shareArticle?mini=true&url=https%3A%2F%2Fmkabumattar.com%2Fcheatsheets%2Fpostgresql&title=PostgreSQL&summary=PostgreSQL%20reference%20guide%20covering%20psql%20commands%2C%20database%20creation%2C%20tables%2C%20queries%2C%20functions%2C%20joins%2C%20transactions%2C%20indexes%2C%20and%20advanced%20SQL%20operations.&source=https://mkabumattar.com "Share on LinkedIn")[WhatsApp](https://wa.me/?text=PostgreSQL%20https%3A%2F%2Fmkabumattar.com%2Fcheatsheets%2Fpostgresql "Share on WhatsApp")[Telegram](https://t.me/share/url?url=https%3A%2F%2Fmkabumattar.com%2Fcheatsheets%2Fpostgresql&text=PostgreSQL "Share on Telegram")[Reddit](https://www.reddit.com/submit?url=https%3A%2F%2Fmkabumattar.com%2Fcheatsheets%2Fpostgresql&title=PostgreSQL "Share on Reddit")[Hacker News](http://news.ycombinator.com/submitlink?u=https%3A%2F%2Fmkabumattar.com%2Fcheatsheets%2Fpostgresql&t=PostgreSQL "Share on Hacker News")[Pinterest](https://pinterest.com/pin/create/button/?url=https%3A%2F%2Fmkabumattar.com%2Fcheatsheets%2Fpostgresql&media=&description=PostgreSQL%20reference%20guide%20covering%20psql%20commands%2C%20database%20creation%2C%20tables%2C%20queries%2C%20functions%2C%20joins%2C%20transactions%2C%20indexes%2C%20and%20advanced%20SQL%20operations. "Share on Pinterest")[Email](<mailto:?subject=PostgreSQL&body=Check out this article: https%3A%2F%2Fmkabumattar.com%2Fcheatsheets%2Fpostgresql>)

## Comments

## You might also enjoy

More posts on similar topics

## [Redis](/cheatsheets/redis)

-   [Mohammad Abu Mattar](/authors/mohammad-abu-mattar)
-   Reference

Redis reference guide covering commands, data types, keys, strings, lists, sets, hashes, sorted sets, transactions, pub/sub, and caching strategies.

[read more](/cheatsheets/redis)

## [Chef](/cheatsheets/chef)

-   [Mohammad Abu Mattar](/authors/mohammad-abu-mattar)
-   Reference

Chef is an Infrastructure as Code platform for automating infrastructure configuration, deployment, and management. This cheatsheet covers Chef concepts, commands, patterns, and best practices for man

[read more](/cheatsheets/chef)

## [Docker Compose](/cheatsheets/docker-compose)

-   [Mohammad Abu Mattar](/authors/mohammad-abu-mattar)
-   Reference

Docker Compose is a tool for defining and running multi-container Docker applications. It uses a YAML file to configure application services, networks, and volumes. This cheatsheet provides a quick re

[read more](/cheatsheets/docker-compose)

## [Dockerfile](/cheatsheets/dockerfile)

-   [Mohammad Abu Mattar](/authors/mohammad-abu-mattar)
-   Reference

This cheat sheet covers the core Dockerfile instructions, best practices, and common pitfalls for building small, secure Docker images. Each section pairs a Dockerfile snippet with the build output it

[read more](/cheatsheets/dockerfile)

## [Ansible](/cheatsheets/ansible)

-   [Mohammad Abu Mattar](/authors/mohammad-abu-mattar)
-   Reference

Ansible is an automation tool for configuration management, application deployment, and orchestration. This cheatsheet is a quick reference for common Ansible tasks, modules, and best practices.

[read more](/cheatsheets/ansible)

## [SSH](/cheatsheets/ssh)

-   [Mohammad Abu Mattar](/authors/mohammad-abu-mattar)
-   Reference

export const components = { h1: 'h1', h2: 'h2', h3: 'h3', h4: 'h4', h5: 'h5', h6: 'h6', p: 'p', a: 'a', ul: 'ul', ol: 'ol', li: 'li', code: 'code', pre: 'pre', strong: 'str

[read more](/cheatsheets/ssh)

6 related posts