CRUD Operations in PostgreSQL

CRUD operations form the backbone of database interactions. Whether you're building web applications, managing data pipelines, or performing data analysis, understanding how to Create, Read, Update, and Delete data efficiently is essential. This comprehensive guide covers all aspects of CRUD operations in PostgreSQL with practical examples and best practices.

What is CRUD?

CRUD stands for Create, Read, Update, and Delete. These are the four basic operations that can be performed on database data:

Setting Up Sample Data

Let's create a locations table to demonstrate each CRUD operation:

CREATE TABLE locations (
  id SERIAL PRIMARY KEY,
  name TEXT UNIQUE NOT NULL,
  latitude NUMERIC CHECK (latitude BETWEEN -90 AND 90),
  longitude NUMERIC CHECK (longitude BETWEEN -180 AND 180),
  description TEXT DEFAULT 'No description available',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE - Inserting Data

Single Record Insertion

INSERT INTO locations (name, latitude, longitude, description)
VALUES ('Central Park', 40.785091, -73.968285, 'A large public park in NYC');

Multiple Records Insertion

INSERT INTO locations (name, latitude, longitude, description) VALUES
('Golden Gate Park', 37.7690, -122.4835, 'San Francisco park'),
('Millennium Park', 41.8826, -87.6226, 'Chicago park'),
('Hyde Park', 51.5074, -0.1278, 'London park');

INSERT Variations

INSERT with RETURNING

INSERT INTO locations (name, latitude, longitude, description)
VALUES ('Prospect Park', 40.6602, -73.9690, 'Brooklyn park')
RETURNING id, name, created_at;

READ - Querying Data

Basic SELECT Operations

-- Select all records
SELECT * FROM locations;

-- Select specific columns
SELECT name, latitude, longitude FROM locations;

-- Filter with WHERE clause
SELECT * FROM locations WHERE latitude > 40;

-- Sort results
SELECT * FROM locations 
WHERE longitude < -70
ORDER BY latitude DESC;

Advanced READ Operations

-- Count records
SELECT COUNT(*) FROM locations;

-- Aggregate functions
SELECT 
  COUNT(*) as total_locations,
  AVG(latitude) as avg_latitude,
  MAX(longitude) as max_longitude
FROM locations;

-- Conditional selection
SELECT 
  name,
  CASE 
    WHEN latitude > 40 THEN 'Northern'
    ELSE 'Southern'
  END as region
FROM locations;

UPDATE - Modifying Data

Single Column Update

UPDATE locations
SET description = 'A famous city park in NYC'
WHERE name = 'Central Park';

Multiple Column Update

UPDATE locations
SET 
  latitude = 40.7850, 
  longitude = -73.9680,
  description = 'Updated coordinates for Central Park'
WHERE name = 'Central Park';

Conditional Updates

-- Update based on conditions
UPDATE locations
SET description = CASE 
  WHEN latitude > 40 THEN description || ' (Northern Location)'
  ELSE description || ' (Southern Location)'
END;

-- Update with calculations
UPDATE locations
SET latitude = latitude + 0.001
WHERE name LIKE '%Park%';

UPDATE with RETURNING

UPDATE locations
SET description = 'A famous city park'
WHERE name = 'Central Park'
RETURNING id, name, description, created_at;

DELETE - Removing Data

Targeted Deletion

-- Delete specific record
DELETE FROM locations WHERE name = 'Millennium Park';

-- Delete with conditions
DELETE FROM locations WHERE latitude < 0;

-- Delete with RETURNING
DELETE FROM locations 
WHERE description = 'No description available'
RETURNING name, id;

Bulk Deletion (Use with Caution!)

-- Delete all records (dangerous!)
DELETE FROM locations;  -- Avoid unless you mean it!

-- Safer approach with explicit condition
DELETE FROM locations WHERE created_at < '2023-01-01';

Safe Deletion Practices

-- Always test with SELECT first
SELECT * FROM locations WHERE name = 'Test Location';

-- Then delete
DELETE FROM locations WHERE name = 'Test Location';

-- Use transactions for safety
BEGIN;
DELETE FROM locations WHERE id = 5;
-- Check the result, then either:
COMMIT;   -- to confirm
-- or
ROLLBACK; -- to undo

UPSERT - INSERT with Conflict Resolution

PostgreSQL's ON CONFLICT clause provides powerful upsert functionality:

-- Insert or update if conflict on unique constraint
INSERT INTO locations (id, name, latitude, longitude, description)
VALUES (1, 'Central Park', 40.785091, -73.968285, 'NYC park')
ON CONFLICT (id)
DO UPDATE SET
  name = EXCLUDED.name,
  latitude = EXCLUDED.latitude,
  longitude = EXCLUDED.longitude,
  description = EXCLUDED.description;

-- Insert or do nothing if conflict
INSERT INTO locations (name, latitude, longitude)
VALUES ('Duplicate Park', 40.7850, -73.9680)
ON CONFLICT (name) DO NOTHING;

Working with Transactions

Transactions ensure data consistency by grouping multiple operations:

-- Basic transaction
BEGIN;

INSERT INTO locations (name, latitude, longitude) 
VALUES ('Test Park', 0, 0);

UPDATE locations 
SET description = 'Test Update' 
WHERE name = 'Test Park';

-- Either commit all changes or rollback
COMMIT; -- or ROLLBACK;

Advanced Transaction Example

-- Complex transaction with error handling
BEGIN;

-- Insert new location
INSERT INTO locations (name, latitude, longitude, description)
VALUES ('New Park', 41.8781, -87.6298, 'Chicago area park');

-- Update related records
UPDATE locations 
SET description = description || ' (Updated in batch)'
WHERE latitude BETWEEN 41 AND 42;

-- Check if everything looks good
SELECT COUNT(*) FROM locations WHERE description LIKE '%(Updated in batch)%';

-- Commit if satisfied, otherwise rollback
COMMIT;

Adding Spatial Data (PostGIS Extension)

For GIS applications, you can enhance your CRUD operations with spatial data:

-- Add geometry column
ALTER TABLE locations ADD COLUMN geom GEOMETRY(Point, 4326);

-- Update geometry from lat/lon
UPDATE locations
SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)
WHERE longitude IS NOT NULL AND latitude IS NOT NULL;

-- Spatial queries
SELECT name, ST_AsText(geom) as coordinates
FROM locations
WHERE ST_DWithin(
  geom, 
  ST_SetSRID(ST_MakePoint(-73.9680, 40.7850), 4326),
  1000  -- 1000 meters
);

Real-World Application Scenarios

Mobile App Data Collection

-- Field agent submits new location
INSERT INTO locations (name, latitude, longitude, description)
VALUES ('Field Survey Point', 40.7589, -73.9851, 'Collected via mobile app')
RETURNING id;

-- Agent corrects location data
UPDATE locations
SET latitude = 40.7590, longitude = -73.9850
WHERE id = 15 AND created_at > CURRENT_DATE;

-- Remove invalid submissions
DELETE FROM locations
WHERE latitude = 0 AND longitude = 0;

Data Synchronization

-- Sync external data source
INSERT INTO locations (name, latitude, longitude, description)
VALUES ('External Location', 42.3601, -71.0589, 'From external API')
ON CONFLICT (name)
DO UPDATE SET
  latitude = EXCLUDED.latitude,
  longitude = EXCLUDED.longitude,
  description = EXCLUDED.description || ' (Updated from API)';

Performance Considerations

Error Handling and Validation

-- Handle constraint violations
DO $$
BEGIN
  INSERT INTO locations (name, latitude, longitude)
  VALUES ('Test Location', 91, 0);  -- Invalid latitude
EXCEPTION
  WHEN check_violation THEN
    RAISE NOTICE 'Invalid latitude value provided';
END $$;

-- Validate before operations
SELECT 
  name,
  CASE 
    WHEN latitude BETWEEN -90 AND 90 THEN 'Valid'
    ELSE 'Invalid latitude'
  END as validation_status
FROM locations;

Best Practices Summary

1. Use transactions: Wrap related operations in transactions for consistency
2. Validate data: Use constraints and checks to maintain data quality
3. Handle conflicts: Use UPSERT patterns for robust data synchronization
4. Return information: Use RETURNING clause to get feedback from operations
5. Test safely: Always test DELETE and UPDATE operations with SELECT first
6. Index appropriately: Create indexes on frequently queried columns
7. Use specific columns: Avoid SELECT * in production code

Common Pitfalls to Avoid

• Missing WHERE clauses: Always double-check UPDATE and DELETE statements
• Ignoring constraints: Understand table constraints before inserting data
• Not using transactions: Group related operations for consistency
• Poor error handling: Plan for constraint violations and data conflicts
• Inefficient queries: Avoid unnecessary full table scans