CRUD operations in PostgreSQL – Chethan S Poojary

CRUD operations in PostgreSQL are the four basic actions you perform on table data: Create, Read, Update, Delete. These are done with standard SQL commands that you run in psql, pgAdmin, or from your backend code.

Setting up a sample table

Before running CRUD operations, you need a table to store data. For this example, imagine a basic users table that could power a user management module in your app. The table contains an auto-incrementing primary key and some typical columns like name, email, and age.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  age INT
);

With this table ready, you can now insert, query, update, and remove user records using standard SQL.

CREATE (INSERT)

Use INSERT to add new rows:

-- single row
INSERT INTO users (name, email, age)
VALUES ('John Doe', 'john@example.com', 30);

-- multiple rows
INSERT INTO users (name, email, age)
VALUES 
  ('Jane Smith', 'jane@example.com', 25),
  ('Bob Johnson', 'bob@example.com', 35);

READ (SELECT)

Use SELECT to read data:

-- all columns, all rows
SELECT * FROM users;

-- specific columns
SELECT name, email FROM users;

-- with filter
SELECT * FROM users WHERE age > 30;

-- with sorting and limit
SELECT * FROM users
ORDER BY age DESC
LIMIT 10;

SELECT is what you’ll typically call from your API GET endpoints.

UPDATE

Use UPDATE to modify existing rows:

-- update a single user
UPDATE users
SET age = 31
WHERE email = 'john@example.com';

-- bulk update (be careful!)
UPDATE users
SET age = age + 1
WHERE age IS NOT NULL;

Always include a WHERE clause; omitting it will update all rows in the table.

DELETE

Use DELETE to remove rows:

-- delete one user
DELETE FROM users
WHERE email = 'bob@example.com';

-- delete all users older than 40
DELETE FROM users
WHERE age > 40;

-- delete ALL rows (use only in special cases)
DELETE FROM users;

Again, the WHERE clause is crucial to avoid deleting unintended data.