SQL
CREATE TABLE products (

id bigserial PRIMARY KEY,

description VARCHAR(255),

embedding vector(384)

);

SQL
INSERT INTO products (description) VALUES

 ('Organic Cotton Baby Onesie - Newborn Size, Blue'), 

 ('Soft Crib Sheet for Newborn, Hypoallergenic'), 

('Baby Monitor with Night Vision and Two-Way Audio'),

('Diaper Bag Backpack with Changing Pad - Unisex Design'),
 ('Stroller for Infants and Toddlers, Lightweight'),

 ('Car Seat for Newborn, Rear-Facing, Extra Safe'),

 ('Baby Food Maker, Steamer and Blender Combo'),

 ('Toddler Sippy Cup, Spill-Proof, BPA-Free'),

 ('Educational Toys for 6-Month-Old Baby, Colorful Blocks'),

 ('Baby Clothes Set - 3 Pack, Cotton, 0-3 Months'),

('High Chair for Baby, Adjustable Height, Easy to Clean'),

('Baby Carrier Wrap, Ergonomic Design for Newborns'),

 ('Nursing Pillow for Breastfeeding, Machine Washable Cover'),

 ('Baby Bath Tub, Non-Slip, for Newborn and Infant'), 

 ('Baby Skincare Products - Lotion, Shampoo, Wash - Organic');

SQL
descriptions = [product[1] for product in products]
model = SentenceTransformer("all-MiniLM-L6-v2")
embeddings = model.encode(descriptions)

# Update the database with embeddings
for i, product in enumerate(products):
product_id = product[0]
embedding = embeddings[i] # Convert to Python list

# Construct the vector string representation
embedding_str = str(embedding.tolist())
cur.execute("UPDATE products SET embedding = %s WHERE id = %s", (embedding_str, product_id))

# Commit changes and close connection
conn.commit()

SQL
SELECT id, description
FROM products
WHERE description @@ to_tsquery('english', 'crib | baby | bed');

SQL
"Soft Crib Sheet for Newborn, Hypoallergenic"

SQL
--Create a tsvector column (you can add this to your existing table) 

ALTER TABLE products ADD COLUMN description_tsv tsvector;

 --Update the tsvector column with indexed data from the description column 

UPDATE products SET description_tsv = to_tsvector('english', description); 

-- Create a GIN index on the tsvector column 

CREATE INDEX idx_products_description_tsv ON products USING gin(description_tsv);

Python
# The query string
query_string = 'baby sleeping accessories'

# Generate embedding for the query string
query_embedding = model.encode(query_string).tolist()

# Construct the SQL query using the cosine similarity operator (<->)
# Assuming you have an index that supports cosine similarity (e.g., ivfflat with vector_cosine_ops)
sql_query = """
SELECT id, description, (embedding <-> %s::vector) as similarity
FROM products
ORDER BY similarity
LIMIT 5;
"""

# Execute the query
cur.execute(sql_query, (query_embedding,))

# Fetch and print the results
results = cur.fetchall()
for result in results:
product_id, description, similarity = result
print(f"ID: {product_id}, Description: {description}, Similarity: {similarity}")

cur.close()
conn.close()

Plain Text
ID: 12, Description: Baby Carrier Wrap, Ergonomic Design for Newborns, Similarity: 0.9956936200879117
ID: 2, Description: Soft Crib Sheet for Newborn, Hypoallergenic, Similarity: 1.0233573590998544
ID: 5, Description: Stroller for Infants and Toddlers, Lightweight, Similarity: 1.078171715208051
ID: 6, Description: Car Seat for Newborn, Rear-Facing, Extra Safe, Similarity: 1.08259154868697
ID: 3, Description: Baby Monitor with Night Vision and Two-Way Audio, Similarity: 1.0902734271784085

SQL
CREATE INDEX ON products USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100); 

lists indicates the number of clusters to create.
vector_cosine_ops indicates the distance metric we are using (cosine, inner product, or Euclidean/L2)