Managing Extensions

SELECT name, default_version, comment FROM pg_available_extensions ORDER BY name;

\dx
SELECT extname, extversion FROM pg_extension;

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE SCHEMA ext;
CREATE EXTENSION pgcrypto WITH SCHEMA ext;

CREATE EXTENSION "uuid-ossp";
SELECT uuid_generate_v4();
-- PG 13+ also has built-in gen_random_uuid()

SELECT crypt('pw', gen_salt('bf', 12));      -- bcrypt
SELECT encode(digest('hello', 'sha256'),'hex');
SELECT encode(hmac('msg','key','sha256'),'hex');

SELECT similarity('postgresql','postgrez');
SELECT * FROM customers WHERE full_name % 'jon smyth';
CREATE INDEX customers_name_trgm ON customers USING gin (full_name gin_trgm_ops);

CREATE EXTENSION hstore;
SELECT 'a=>1, b=>2'::hstore -> 'a';
-- Today: prefer JSONB for new designs

CREATE EXTENSION citext;
CREATE TABLE users (email citext UNIQUE);    -- case-insensitive equality

CREATE EXTENSION ltree;
SELECT 'a.b.c'::ltree <@ 'a.b';      -- true (descendant)

-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION pg_stat_statements;
SELECT queryid, calls, total_exec_time, mean_exec_time, query
  FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;

CREATE EXTENSION postgis;
SELECT ST_AsText(ST_Point(-122.42, 37.77));
SELECT * FROM shops
 WHERE ST_DWithin(location, ST_MakePoint(-122,37)::geography, 1000);

ALTER EXTENSION pg_trgm UPDATE;
ALTER EXTENSION pg_trgm UPDATE TO '1.6';

DROP EXTENSION IF EXISTS pg_trgm CASCADE;