PostgreSQL Complete Beginner → Developer Cheatsheet (In-Depth)
Updated
•3 min read
D
I’m a Computer Science Engineer focused on backend development, scalable system design, and real-world production deployments. I build and deploy full-stack applications using Node.js, Express, MongoDB, Redis, Docker, and CI/CD pipelines.
Windows: Start & Stop PostgreSQL Service
When PostgreSQL is installed on Windows, it runs as a background service.
Run Command Prompt as Administrator.
net start postgresql-x64-18
Stop PostgreSQL
net stop postgresql-x64-18
Version number may differ (15, 16, 17, 18 etc.)
1️⃣ Connect to Postgres
psql -U postgres
2️⃣ Create Database
CREATE DATABASE salescrm;
Switch to database:
\c salescrm
3️⃣ Create Table
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100) UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Breakdown
| Keyword | Meaning |
|---|---|
| SERIAL | Auto increment integer |
| PRIMARY KEY | Unique row identifier |
| UNIQUE | No duplicate values allowed |
| DEFAULT | Auto assigned value |
| CURRENT_TIMESTAMP | Current date & time |
4️⃣ Insert Data
INSERT INTO users (name, email)
VALUES ('Deepansh', 'deep@gmail.com');
5️⃣ Read Data
SELECT * FROM users;
Filter:
SELECT * FROM users WHERE name = 'Deepansh';
6️⃣ Update Data
UPDATE users
SET name = 'Mr Deepansh'
WHERE id = 1;
7️⃣ Delete Data
DELETE FROM users WHERE id = 1;
📌 Database Related
| Command | Meaning |
|---|---|
\l |
List databases |
\c dbname |
Connect to database |
\conninfo |
Show current connection info |
📌 Table Related
| Command | Meaning |
|---|---|
\dt |
List tables |
\d tablename |
Describe table |
\d+ tablename |
More detailed info |
\di |
List indexes |
\dv |
List views |
📌 User / Role Related
| Command | Meaning |
|---|---|
\du |
List roles/users |
📌 Help
| Command | Meaning |
|---|---|
\? |
Show all psql commands |
\h |
SQL help |
Example:
\h CREATE TABLE
🧠 Difference Between SQL & psql Commands
| SQL | psql |
|---|---|
SELECT * FROM users; |
\dt |
CREATE TABLE |
\l |
Ends with ; |
No ; needed |
🎯 Why These Are Important?
Because in real backend development:
\dtquickly check table\ddebug structure\dicheck indexes\dumanage roles
Faster than writing SQL queries.
🚀 Now Small Practice
Inside psql run:
\di
Then:
\du
