âš¡IntermediateStored Programs
PostgreSQL Extensions
Extend PostgreSQL functionality with installable modules using CREATE EXTENSION.
Overview
PostgreSQL's extension system lets you add functionality without modifying the core. Extensions bundle new data types, functions, operators, and index methods. The most popular extensions are uuid-ossp (UUID generation), pg_trgm (fuzzy text search), hstore (key-value pairs), PostGIS (geospatial), and pgcrypto (encryption).
Extensions are per-database. Install them with CREATE EXTENSION in each database that needs them. Check available extensions with SELECT * FROM pg_available_extensions.
Key Points
- •CREATE EXTENSION IF NOT EXISTS extension_name
- •Extensions are database-scoped install in each DB that needs them
- •pg_trgm: fast LIKE/ILIKE, trigram similarity
- •uuid-ossp: uuid_generate_v4() and other UUID functions
- •pgcrypto: gen_salt(), crypt() for password hashing
- •hstore: key-value column type
Examples
Common extensions
-- Install extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
-- Use pg_trgm for fuzzy search
SELECT word_similarity('postgres', 'postgresql');
SELECT * FROM products WHERE name % 'postgress'; -- fuzzy match
-- Use pgcrypto for password hashing
SELECT crypt('mypassword', gen_salt('bf', 8));
-- List installed extensions
SELECT extname, extversion FROM pg_extension;Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4