Extensions

Extensions are how PostgreSQL adds functionality without bloating the core server — install only what you actually need, and it behaves as if it always shipped that way.

Listing available and installed extensions

\dx

                 List of installed extensions
  Name   | Version |   Schema   |         Description          
---------+---------+------------+-------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

plpgsql — the procedural language used to write stored functions and triggers — is installed by default in every new database. Everything else is opt-in.

Installing pgcrypto

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE EXTENSION

pgcrypto adds cryptographic functions directly usable in SQL — hashing, encryption, and (on older PostgreSQL versions) the gen_random_uuid() function from the Data Types lesson. IF NOT EXISTS means re-running this on a database that already has it installed is harmless.

Using what it adds

SELECT crypt('correct-horse-battery-staple', gen_salt('bf')) AS hashed;

                            hashed                            
---------------------------------------------------------------
 $2a$06$eImiTXuWVxfM37uY4JANjQ==k9NHzs.pv1QoZjuz8H8f...
(1 row)

crypt() and gen_salt('bf') together produce a bcrypt password hash directly in SQL — the same style of hash you'd more commonly generate in application code (a language's own bcrypt library), but available here if you ever need it at the database level.

Other extensions worth knowing exist

• uuid-ossp — an older, alternative source of UUID-generating functions, largely superseded by the built-in gen_random_uuid() on modern PostgreSQL.
• postgis — adds geographic/spatial data types and queries, turning PostgreSQL into a full GIS database.
• pg_trgm — trigram-based text matching, useful for fast fuzzy/similarity search beyond what LIKE can do.