The core of working with PostgreSQL is CRUD operations: Create (INSERT), Read (SELECT), Update (UPDATE), and Delete (DELETE). INSERT INSERT INTO adds rows to a table. PostgreSQL extends standard SQL with RETURNING to return inserted data, and ON CONFLICT for upsert behavior. SELECT SELECT retrieves data. It supports: WHERE — filter rows with conditions ORDER BY — sort results (ASC/DESC) LIMIT / OFFSET — paginate results DISTINCT — remove duplicate rows AS — column aliases for readability Comparison Operators PostgreSQL supports standard operators: =, != or <>, <, >, <=, >=, and also BETWEEN, IN, LIKE, ILIKE (case-insensitive), IS NULL, IS NOT NULL. Logical Operators Combine conditions with AND, OR, and NOT. Use parentheses to control evaluation order.
Key Takeaways
RETURNING clause lets you get inserted/updated/deleted data back immediately.
ON CONFLICT enables upsert behavior without separate SELECT + INSERT.
Use ILIKE for case-insensitive pattern matching.
The && operator checks array overlap; @> checks array containment.