PostgreSQL Cheat Sheet You Should Know

Knowing your way around PostgreSQL is not just a skill but a necessity in today’s data-driven world. This article aims to offer a handy cheat sheet to assist both newcomers and experienced developers in navigating the intricacies of PostgreSQL, covering everything from installation to essential SQL queries.

Introduction

PostgreSQL is an open-source relational database management system known for its extensibility, standards compliance, and robustness. Given the increasingly complex nature of applications and the diversity of data storage needs, mastering PostgreSQL commands can significantly boost your efficiency and capabilities as a developer.

Installation and Setup

Before diving into the commands and queries, it’s crucial to ensure that your system is ready and PostgreSQL is correctly installed.

System Requirements

While PostgreSQL is quite versatile, it’s essential to note that you need at least:

64-bit CPU architecture
Minimum 512 MB of RAM (2 GB recommended)
Disk space varies with use, but initial setup would require at least 300 MB.

Installation Steps for Various OS

Linux (Ubuntu): Use apt-get commands:
sudo apt-get update sudo apt-get install postgresql postgresql-contrib
Windows: Download the installer from official website
macOS: Use Homebrew:
brew install postgresql

Basic Configuration

After installation, you’ll usually want to set the default user password:

sudo -u postgres psql

\password postgres

Connecting to a Database

Whether you are developing locally or handling a remote database, connecting is your first step.

Using psql

The psql command-line tool is the most straightforward way to connect:

psql -U username -d database_name

Connection Strings

In programming languages like Python or Node.js, you would often use a connection string:

DATABASE_URL = "postgresql://username:password@localhost:port/database_name"

Connection Poolers

For scalable applications, connection pooling like PgBouncer or Pgpool-II can be invaluable.

Basic Commands

Now, let’s review some of the basic commands to get you started.

Listing Databases

To list all databases, use \l or \list in the psql interface.

Switching Databases

Switch databases with \c database_name.

Displaying Tables

Display all tables in the current database with \dt.

SQL Basics

The power of PostgreSQL is harnessed through SQL queries. Here’s a brief overview.

SELECT Statements

To fetch data, you’ll mostly be using SELECT queries:

SELECT column_name FROM table_name WHERE condition;

INSERT Statements

To insert data:

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

UPDATE Statements

To update existing records:

UPDATE table_name SET column1 = value1 WHERE condition;

DELETE Statements

To remove records:

DELETE FROM table_name WHERE condition;

Data Types

Understanding the various data types supported can make your work with PostgreSQL much smoother.

Numeric Types

INTEGER
FLOAT
DECIMAL

Text Types

CHAR(n)
VARCHAR(n)
TEXT

Date/Time Types

PostgreSQL offers a variety of date/time types including timestamp, timestamptz, date, and time. The timestamp type is useful for recording when events occur, and the timestamptz is a timezone-aware version of it.

Example:

CREATE TABLE events (

    id SERIAL PRIMARY KEY,

    name VARCHAR(100),

    event_time TIMESTAMP

);

For further reading, you can check the official documentation on date/time types.

Boolean, Enum, and Others

PostgreSQL supports the boolean data type for true/false values and enum for custom data types with a discrete set of static values.

Example:

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

CREATE TABLE person (

    name VARCHAR(100),

    current_mood mood

);

Working with Indexes

Indexes are essential for speeding up database queries. In PostgreSQL, there are several types of indexes, the most common of which is the B-tree index.

Creating an Index

To create an index, you can use the CREATE INDEX command.

Example:

CREATE INDEX idx_name ON table_name (column_name);

Viewing Indexes

You can view existing indexes using the \di command in the psql terminal or query the pg_indexes system catalog.

Example:

SELECT * FROM pg_indexes WHERE tablename = 'table_name';

Deleting an Index

Deleting an index can be done using the DROP INDEX command.

DROP INDEX idx_name;

Transactions

Transactions are a way to ensure data integrity.

BEGIN, COMMIT, ROLLBACK

BEGIN initiates a transaction, COMMIT finalizes the changes, and ROLLBACK undoes them.

Example:

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;

COMMIT;

SAVEPOINT and ROLLBACK TO

SAVEPOINT allows you to set a point within a transaction to which you can later roll back.

BEGIN;

SAVEPOINT my_savepoint;

-- do something

ROLLBACK TO my_savepoint;

Joins and Subqueries

INNER JOIN

INNER JOIN combines rows from two or more tables based on a related column.

SELECT customers.name, orders.order_id FROM customers

INNER JOIN orders ON customers.customer_id = orders.customer_id;

LEFT/RIGHT OUTER JOIN

LEFT JOIN or RIGHT JOIN returns records from one table and matching records from another.

SELECT students.name, scores.score FROM students

LEFT JOIN scores ON students.student_id = scores.student_id;

Subqueries

Subqueries are used to retrieve data that will be used in the main query as a condition.

SELECT name FROM students WHERE id IN (SELECT student_id FROM scores WHERE score > 90);

Aggregation and Grouping

COUNT, SUM, AVG

Aggregate functions like COUNT, SUM, and AVG provide a way to perform operations on a set of rows.

SELECT COUNT(*) FROM students;

GROUP BY, HAVING

GROUP BY is used to group rows that have the same values in specified columns. HAVING is often used with GROUP BY to filter groups.

SELECT department, COUNT(*) FROM employees

GROUP BY department

HAVING COUNT(*) > 10;

Functions and Stored Procedures

Built-in Functions

PostgreSQL comes with a rich set of built-in functions. For example, NOW() returns the current date and time.

Creating Custom Functions

You can create your own functions using PL/pgSQL.

CREATE FUNCTION increment(i integer) RETURNS integer AS $

BEGIN

  RETURN i + 1;

END;

$ LANGUAGE plpgsql;

Triggers

Triggers are named database objects that are associated with a table and get activated when specified conditions are met.

CREATE TRIGGER check_update

BEFORE UPDATE ON accounts

FOR EACH ROW EXECUTE FUNCTION check_account_balance();

Optimization

EXPLAIN and ANALYZE

The EXPLAIN command shows the query plan. Adding ANALYZE actually executes the query.

EXPLAIN ANALYZE SELECT * FROM students;

Query Performance Tips

Use indexes effectively.
Reduce the number of joins.
Use the LIMIT clause where applicable.

Backup and Restore

pg_dump and pg_restore

pg_dump is used for backing up a PostgreSQL database and pg_restore is used to restore it.

pg_dump dbname > outfile

pg_restore -d dbname infile

Continuous Archiving

Continuous archiving can be set up to keep a continuous set of transaction log records.

User and Permission Management

CREATE USER/ROLE

Creating a new user or role can be done using the CREATE USER or CREATE ROLE command.

CREATE USER username WITH PASSWORD 'password';

GRANT and REVOKE Permissions

Permissions can be granted or revoked using GRANT and REVOKE.

GRANT ALL PRIVILEGES ON database_name TO username;

ALTER and DROP USER/ROLE

You can alter or drop a user using ALTER USER and DROP USER.

ALTER USER username WITH PASSWORD 'newpassword';

DROP USER username;

Advanced Features

Partitioning

Table partitioning can be done to improve performance and management of large tables.

Replication

Replication is the practice of sharing information between databases to ensure consistency.

Full-text Search

PostgreSQL supports full-text search natively.

Conclusion

We’ve covered a lot of ground here. Hopefully, this serves as a useful cheat sheet for your PostgreSQL needs.

Introduction

Installation and Setup

System Requirements

Installation Steps for Various OS

Basic Configuration

Connecting to a Database

Using psql

Connection Strings

Connection Poolers

Basic Commands

Listing Databases

Switching Databases

Displaying Tables

SQL Basics

SELECT Statements

INSERT Statements

UPDATE Statements

DELETE Statements

Data Types

Numeric Types

Text Types

Date/Time Types

Boolean, Enum, and Others

Working with Indexes

Creating an Index

Viewing Indexes

Deleting an Index

Transactions

BEGIN, COMMIT, ROLLBACK

SAVEPOINT and ROLLBACK TO

Joins and Subqueries

INNER JOIN

LEFT/RIGHT OUTER JOIN

Subqueries

Aggregation and Grouping

COUNT, SUM, AVG

GROUP BY, HAVING

Functions and Stored Procedures

Built-in Functions

Creating Custom Functions

Triggers

Optimization

EXPLAIN and ANALYZE

Query Performance Tips

Backup and Restore

pg_dump and pg_restore

Continuous Archiving

User and Permission Management

CREATE USER/ROLE

GRANT and REVOKE Permissions

ALTER and DROP USER/ROLE

Advanced Features

Partitioning

Replication

Full-text Search

Conclusion

No comments so far