liveBook · Manning

Create extension command

As explained in Just Use Postgres!

The CREATE EXTENSION command installs and enables a PostgreSQL extension in the current database.

CREATE EXTENSION installs and enables a PostgreSQL extension for the current database. Check pg_available_extensions first: default_version shows what the server provides, while installed_version stays empty until the extension is enabled. section 7.2.2 section 8.2

A practical enablement workflow is:

  • Query pg_available_extensions to confirm the extension exists and identify its available version. section 7.2.2
  • Run CREATE EXTENSION pgcrypto; or CREATE EXTENSION vector; in the target database. section 7.2.2 section 8.2
  • Query installed_version or pg_extension to verify that the extension is enabled for the current database. section 7.2.2 section 8.2

Enable pgcrypto for password handling

Run CREATE EXTENSION pgcrypto;, then use crypt('SuperSecret123', gen_salt('bf')) when inserting a password hash. Authentication compares crypt('SuperSecret123', password_hash) with the stored hash; an incorrect password returns no rows. section 7.2.2

Listing 7.1 Checking the pgcrypto extension version
SELECT name, default_version, installed_version 
FROM pg_available_extensions 
WHERE name = 'pgcrypto';

Inspect the available and installed pgcrypto versions before and after running CREATE EXTENSION. section 7.2.2

How Just Use Postgres! covers it

How deep it goes
Explains availability checks, installation, verification, and practical use, but does not cover comprehensive administration or deployment guidance.
Examples
Demonstrates pgcrypto password hashing and authentication, then enables pgvector for storing vector embeddings.
What it assumes
Assumes a running PostgreSQL server and database connection, while demonstrating extension availability checks from the beginning.
What you can do after
Apply it via PostgreSQL SQL to enable an available extension and verify that its database features are ready to use.
How it connects
Connects PostgreSQL extensions with pgcrypto, pgvector, cryptographic functions, vector data types, and Docker-based PostgreSQL.
Cover of Just Use Postgres!
liveBookmanning.com
Read Just Use Postgres! on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of Just Use Postgres!. The section links point to the parts of the book it draws on.