pg_similarity

CREATE TABLE products (
  tenant_id uuid,
  id integer,
  name text,
  description text,
  PRIMARY KEY (tenant_id, id)
);

-- Create a tenant first
INSERT INTO tenants (id, name) VALUES
  ('d1c06023-3421-4fbb-9dd1-c96e42d2fd02', 'Tenant 1');

INSERT INTO products (tenant_id, id, name, description) VALUES
  ('d1c06023-3421-4fbb-9dd1-c96e42d2fd02', 1, 'Laptop Computer', 'High-performance laptop'),
  ('d1c06023-3421-4fbb-9dd1-c96e42d2fd02', 2, 'Laptop Computr', 'High-performance notebook'),
  ('d1c06023-3421-4fbb-9dd1-c96e42d2fd02', 3, 'Desktop Computer', 'Powerful desktop workstation');

-- Using Levenshtein for typo detection
SELECT name,
       lev(name, 'Laptop Computer') as similarity
FROM products
ORDER BY similarity DESC;

-- Using operator syntax
SELECT name
FROM products
WHERE name ~== 'Laptop Computer';

-- Using threshold to show only more similar results
SET pg_similarity.levenshtein_threshold = 0.8;

SELECT name
FROM products
WHERE name ~== 'Laptop Computer';

-- Using Cosine for content matching
SELECT name,
       cosine(description, 'high performance notebook') as similarity
FROM products
WHERE cosine(description, 'high performance notebook') > 0.5
ORDER BY similarity DESC;

-- Using operator syntax
-- This will return nothing because the threshold is 0.7
SELECT name
FROM products
WHERE description ~## 'high performance notebook';

-- Set threshold for specific algorithm
SET pg_similarity.cosine_threshold = 0.3;

-- Now this will return the results
SELECT name
FROM products
WHERE description ~## 'high performance notebook';

SET pg_similarity.cosine_threshold = 0.3;

RESET pg_similarity.cosine_threshold;