PostgreSQL Cheat Sheet: Mastering CREATE, SELECT, INSERT, UPDATE, and DELETE!

PostgreSQL

"A good database is like a healthy relationship: stable, well-structured, and doesn’t crash easily. Start learning PostgreSQL now!"

(Because Who Has Time to Read the Whole Manual?)

Welcome to the World of SQL Commands!

So, you’ve installed PostgreSQL. Congratulations!  Now what?

Well, my friend, you’ve entered the world of SQL commands—where a few keystrokes can create entire databases or, if you’re not careful, accidentally delete everything in seconds. 

But don’t worry! Today, we’ll break down the five essential PostgreSQL commands every beginner needs:

• CREATE – Build databases & tables 
• SELECT – Retrieve data like a hacker 
• INSERT – Add new data 
• UPDATE – Modify existing data 
• DELETE – Remove data (or cause mayhem) 

Let’s jump in before your database turns into a disaster zone!

CREATE – The Architect of Your Database

Before you can store anything, you need to CREATE databases and tables. Think of it like building a house before moving in.

Creating a Database

CREATE DATABASE my_first_database;

Boom! You just created your first PostgreSQL database!

To check if it exists, run:

\l  -- Lists all databases

To connect to the database:

\c my_first_database

Creating a Table

Tables are where your data lives. Let’s create a customers table:

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,  -- Auto-incrementing ID
    name VARCHAR(100) NOT NULL,  -- Customer name (required)
    email TEXT UNIQUE,  -- Email (must be unique)
    created_at TIMESTAMP DEFAULT now()  -- Auto-fills current time
);

Congratulations, your database now has a home for customer data!

To see all tables in your database:

\dt  -- Lists all tables

SELECT – Finding Data Like a Detective

The SELECT command lets you retrieve data from tables—because what’s the point of storing data if you can’t find it?

Selecting Everything

Want to see all customer data? Use:

SELECT * FROM customers;

This shows everything in the customers table.

Selecting Specific Columns

Need just the customer names and emails?

SELECT name, email FROM customers;

Filtering Results with WHERE

Want to find a specific customer?

SELECT * FROM customers WHERE name = 'Alice';

Sorting Results with ORDER BY

Want results in alphabetical order?

SELECT * FROM customers ORDER BY name ASC;  -- ASC = Ascending

INSERT – Adding Data 

What’s a database without data? Empty and sad. Let’s fix that by using INSERT.

Insert a New Customer

INSERT INTO customers (name, email) 
VALUES ('Alice', 'alice@email.com');

Now Alice is officially in the system!

Insert Multiple Rows at Once

INSERT INTO customers (name, email) VALUES 
('Bob', 'bob@email.com'),
('Charlie', 'charlie@email.com');

Batch insert! Now your database isn’t lonely anymore.

Checking If It Worked

Run:

SELECT * FROM customers;

Congrats, you just added new data!

UPDATE – Changing Data Without Breaking Stuff

Data changes all the time. People get new emails, change names, or give fake information (looking at you, "John Doe"). That’s where UPDATE comes in.

Updating a Single Record

Oops! Alice changed her email. Let’s fix it:

UPDATE customers 
SET email = 'alice.new@email.com' 
WHERE name = 'Alice';

Now Alice has a shiny new email!

Updating Multiple Records

Everyone joined a VIP program? Let’s update them all:

ALTER TABLE customers ADD COLUMN vip_status BOOLEAN DEFAULT FALSE;  -- Add VIP column
UPDATE customers SET vip_status = TRUE WHERE email LIKE '%@email.com';

Now all customers with "@email.com" are VIPs! 

Warning: Without WHERE, this updates EVERY record. 

DELETE – Removing Data (Handle with Care!)

Sometimes, data must go. But be careful—DELETE is permanent!

Deleting a Single Customer

DELETE FROM customers WHERE name = 'Charlie';

Charlie has been erased from existence.

Deleting All Customers (Oops!)

WARNING: THIS WILL DELETE EVERYTHING!

DELETE FROM customers;

If you accidentally run this, you might cry.

To delete a table entirely:

DROP TABLE customers;

Gone. Forever. No undo button.

Bonus: TRUNCATE – DELETE on Steroids

DELETE removes rows one by one, but TRUNCATE is like setting your database on fire.

TRUNCATE TABLE customers;

All records deleted—FAST! But structure remains.

Wrapping Up: Your PostgreSQL Power-Up Guide

Now you’re officially fluent in PostgreSQL basics! Let’s recap:

CREATE – Builds databases & tables
SELECT – Retrieves data
INSERT – Adds new records
UPDATE – Modifies existing data
DELETE – Removes data (handle with care!) 

 Bonus Tips:

• Always use WHERE with UPDATE and DELETE to avoid disasters!
• Use ORDER BY to organize your results neatly.
• Back up your database before testing DELETE commands!

just don’t accidentally delete everything.