INSERT, SELECT, UPDATE, DELETE, and Everyday CRUD Workflows
Master core PostgreSQL CRUD statements and understand how application actions map to SQL operations.
Inside this chapter
- CRUD as the Language of Applications
- Creating and Reading Records
- Updating and Deleting Carefully
- RETURNING Makes PostgreSQL Convenient
Series navigation
Study the chapters in sequence for the clearest path from beginner PostgreSQL concepts to advanced query design and production operations. Use the navigation at the bottom of every page to move chapter by chapter.
CRUD as the Language of Applications
Most business applications repeatedly create records, read data for pages and APIs, update fields based on user actions, and delete or archive old information. These operations are collectively called CRUD. Even when a framework hides SQL from the developer, the application still depends on these same database patterns underneath.
Creating and Reading Records
INSERT INTO customers (full_name, email)
VALUES ('Anita Rao', 'anita@example.com');
SELECT customer_id, full_name, email
FROM customers;Beginners should notice that PostgreSQL returns exactly the columns requested. It is a good habit to avoid SELECT * in production-style code unless there is a strong reason.
Updating and Deleting Carefully
UPDATE customers
SET full_name = 'Anita S. Rao'
WHERE customer_id = 1;
DELETE FROM customers
WHERE customer_id = 99;WHERE clause before running UPDATE or DELETE. Without it, every row in the table can be changed.
RETURNING Makes PostgreSQL Convenient
INSERT INTO products (sku, product_name, unit_price)
VALUES ('BK-101', 'Database Handbook', 799.00)
RETURNING product_id, product_name;The RETURNING clause is one of PostgreSQL’s very practical features. It lets applications immediately capture inserted or updated values without extra round trips, which is useful in APIs and service code.