What are PostgreSQL extensions

· Category: SQL & Databases

Short answer

Extensions are modular packages that add extra functionality to PostgreSQL, such as spatial data support, cryptography, and full-text search dictionaries.

Steps

  1. List available extensions: SELECT * FROM pg_available_extensions;
  2. Install an extension: CREATE EXTENSION IF NOT EXISTS pgcrypto;
  3. Use extension features: SELECT gen_random_uuid();
  4. Popular extensions include PostGIS for geospatial data, pg_trgm for similarity search, and uuid-ossp for UUID generation.
  5. Drop with DROP EXTENSION pgcrypto;

Tips

  • Extensions are installed per database, not cluster-wide.
  • Some extensions require additional shared libraries in postgresql.conf.

Common issues

  • Missing operating system packages prevent some extensions from installing.
  • Upgrading extensions may require running ALTER EXTENSION ... UPDATE.r