
Get Started in PostgreSql
Getting Started with PostgreSQL 🚀
This guide will help you set up PostgreSQL, create databases, users, and run basic queries.
1. Verify PostgreSQL Installation
Check if PostgreSQL is installed by running:
psql --version
If not installed, refer to the installation guide based on your OS.
2. Access PostgreSQL
- Linux/macOS: Use
sudo -u postgres psql
- Windows: Open
psql
from the PostgreSQL installation folder
Alternatively, connect with:
psql -U postgres
(If prompted, enter the PostgreSQL password)
3. Create a Database
To create a new database:
CREATE DATABASE mydb;
To connect to the database:
\c mydb
4. Create a User and Grant Permissions
CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword';GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
To give only read access:
GRANT CONNECT ON DATABASE mydb TO myuser;GRANT USAGE ON SCHEMA public TO myuser;GRANT SELECT ON ALL TABLES IN SCHEMA public TO myuser;
5. Create a Table
CREATE TABLE users ( id SERIAL PRIMARY KEY, name VARCHAR(100), email VARCHAR(150) UNIQUE, created_at TIMESTAMP DEFAULT NOW());
6. Insert Data into the Table
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com'), ('Bob', 'bob@example.com');
7. Query Data
🔹 Fetch all records:
SELECT * FROM users;
🔹 Fetch a specific user:
SELECT * FROM users WHERE email = 'alice@example.com';
8. Update and Delete Data
🔹 Update a user's name:
UPDATE users SET name = 'Alicia' WHERE email = 'alice@example.com';
🔹 Delete a user:
DELETE FROM users WHERE email = 'bob@example.com';
9. Add an Index (for Performance Optimization)
CREATE INDEX idx_users_email ON users(email);
10. Backup and Restore
🔹 Backup Database
pg_dump -U postgres -W -F c -b -v -f mydb_backup.dump mydb
🔹 Restore Database
pg_restore -U postgres -d mydb -v mydb_backup.dump
Next Steps
✅ Integrate PostgreSQL with Go Fiber
✅ Use pgAdmin for a GUI experience
✅ Learn Joins, Aggregations, and Transactions