PostgreSQL Database Cheatsheet

PostgreSQL is a powerful open-source relational database management system that is widely used for storing and managing data. Whether you are a beginner or an experienced developer, having a cheatsheet handy can save time and simplify your interactions with PostgreSQL. This cheatsheet provides a quick reference guide for common PostgreSQL commands and tasks.

Installation and Connection

Installation

# Ubuntu
sudo apt-get update
sudo apt-get install postgresql

# macOS
brew install postgresql

Connect to Database

psql -U username -d database_name -h host -p port

Replace username, database_name, host, and port with your specific values.

Basic Commands

Create Database

CREATE DATABASE dbname;

Create User

CREATE USER username WITH PASSWORD 'password';

Grant Privileges

GRANT ALL PRIVILEGES ON DATABASE dbname TO username;

List Databases

\l

List Tables

\dt

Data Types

Integer

CREATE TABLE example (
  id INTEGER
);

Text

CREATE TABLE example (
  name TEXT
);

Date

CREATE TABLE example (
  birthdate DATE
);

CRUD Operations

Insert Data

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

Select Data

SELECT column1, column2 FROM table_name WHERE condition;

Update Data

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

Delete Data

DELETE FROM table_name WHERE condition;

Advanced Queries

Join Tables

SELECT * FROM table1
JOIN table2 ON table1.column = table2.column;

Aggregate Functions

SELECT COUNT(*), AVG(column) FROM table_name;

Subqueries

SELECT column FROM table_name
WHERE column IN (SELECT column FROM another_table);

Indexing

Create Index

CREATE INDEX index_name ON table_name (column);

Remove Index

DROP INDEX index_name;

Backup and Restore

Backup

pg_dump -U username -d dbname > backup.sql

Restore

psql -U username -d dbname < backup.sql

This PostgreSQL cheatsheet serves as a quick reference for essential commands and tasks. Keep it handy to streamline your interactions with PostgreSQL and boost your productivity. Remember that this cheatsheet is not exhaustive, and there is much more to explore in the vast world of PostgreSQL. As you delve deeper into database management, refer to the official documentation for more advanced features and best practices.

FAQ