Extensions (pg_trgm, uuid-ossp, hstore) in PostgreSQL

Introduction

Extensions add extra features to your database that are not included by default. They help you do special tasks easily.

You want to search text faster and find similar words (pg_trgm).

You need to create unique IDs automatically (uuid-ossp).

You want to store flexible key-value pairs in one column (hstore).

Syntax

PostgreSQL

CREATE EXTENSION extension_name;

Replace extension_name with the name of the extension you want.

You need proper permissions to add extensions.

Examples

This adds the pg_trgm extension for fast text search using trigrams.

PostgreSQL

CREATE EXTENSION pg_trgm;

This adds the uuid-ossp extension to generate UUIDs.

PostgreSQL

CREATE EXTENSION "uuid-ossp";

This adds the hstore extension to store key-value pairs.

PostgreSQL

CREATE EXTENSION hstore;

Sample Program

This script adds the three extensions if they are not already installed. Then it creates a table using UUIDs for IDs, hstore for flexible data, and uses pg_trgm to find similar text.

PostgreSQL

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS hstore;

-- Create a table using these extensions
CREATE TABLE example (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  data hstore,
  description TEXT
);

-- Insert sample data
INSERT INTO example (data, description) VALUES ('"color"=>"blue", "size"=>"medium"', 'A blue medium item');

-- Search using pg_trgm similarity
SELECT id, description
FROM example
WHERE description % 'blue';

Important Notes

Use IF NOT EXISTS to avoid errors if the extension is already installed.

Extensions like uuid-ossp provide useful functions such as uuid_generate_v4() to create unique IDs.

The pg_trgm extension helps with fast and fuzzy text searches using the % operator.

Summary

Extensions add extra tools to your PostgreSQL database.

pg_trgm helps with fast text similarity searches.

uuid-ossp helps generate unique IDs automatically.

hstore lets you store flexible key-value data in one column.