Why CRUD operations are fundamental in PostgreSQL

Introduction

CRUD operations let us create, read, update, and delete data in a database. They are the basic actions to manage information easily and keep it organized.

When adding new user information to a website

When showing a list of products to customers

When updating a customer's address after they move

When removing old or incorrect data from a system

Syntax

PostgreSQL

CREATE: INSERT INTO table_name (columns) VALUES (values);
READ: SELECT columns FROM table_name WHERE condition;
UPDATE: UPDATE table_name SET column = value WHERE condition;
DELETE: DELETE FROM table_name WHERE condition;

Each operation works on a table in the database.

Conditions help target specific rows to read, update, or delete.

Examples

Adds a new user named Alice who is 30 years old.

PostgreSQL

INSERT INTO users (name, age) VALUES ('Alice', 30);

Gets all users older than 25 years.

PostgreSQL

SELECT * FROM users WHERE age > 25;

Changes Alice's age to 31.

PostgreSQL

UPDATE users SET age = 31 WHERE name = 'Alice';

Removes the user named Alice from the database.

PostgreSQL

DELETE FROM users WHERE name = 'Alice';

Sample Program

This program creates a users table, adds a user named Bob, shows the data, updates Bob's age, shows the data again, deletes Bob, and finally shows the empty table.

PostgreSQL

CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(50), age INT);
INSERT INTO users (name, age) VALUES ('Bob', 25);
SELECT * FROM users;
UPDATE users SET age = 26 WHERE name = 'Bob';
SELECT * FROM users;
DELETE FROM users WHERE name = 'Bob';
SELECT * FROM users;

Important Notes

Always use WHERE clause in UPDATE and DELETE to avoid changing or removing all rows accidentally.

CRUD operations are the foundation for most database tasks and applications.

Summary

CRUD stands for Create, Read, Update, Delete.

They help manage data in a simple and organized way.

Knowing CRUD is essential for working with databases.