PostgreSQL — Extensions

What are Extensions?

Extensions add functionality to PostgreSQL.

List Extensions

SELECT * FROM pg_extension;

Enable Extension

-- UUID generation
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Full text search
CREATE EXTENSION IF NOT EXISTS "pg_trgm";

-- UUID v4
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

Common Extensions

Extension	Description
uuid-ossp	UUID generation
pg_trgm	Trigram similarity
pgcrypto	Cryptographic functions
postgis	Geospatial data
hstore	Key-value store
pg_stat_statements	Query statistics

Disable Extension

DROP EXTENSION "uuid-ossp";

Extension Usage

-- Generate UUID
SELECT uuid_generate_v4();

-- Trigram search
SELECT * FROM products WHERE name % 'phone';

Mini Practice

1. List extensions
2. Enable an extension
3. Use extension functions
4. Disable an extension

Up Next

Continue with Roles — user roles.