-- Generated from trenches-no-python.pdf at https://momjian.us/presentations
-- This is intended to be run by psql so backslash commands are processed.

-- setup
\pset footer off
\pset null (null)

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE document (
    id SERIAL PRIMARY KEY,
    content TEXT NOT NULL
);

CREATE TABLE document_embedding (
    id INTEGER PRIMARY KEY,
    -- ChatGPT4's text embedding vectors have 1536 dimensions
    embedding vector(1536) NOT NULL
);

INSERT INTO document (content) VALUES
        ('The king is a tall man.'),
        ('The queen is a beautiful woman.'),
        ('They sit together in the throne room of the 
castle.');

-- It is performant to create HNSW indexes on empty tables, 
CREATE INDEX document_embedding_embedding_idx ON 
document_embedding 
    USING hnsw (embedding vector_l2_ops);

-- show embeddings for documents
SELECT content,
       substring(embedding::text, 1, 30) AS embedding
FROM document JOIN document_embedding USING (id);

-- each dimension is four bytes (float4)
SELECT pg_column_size(embedding),
       pg_column_size(embedding) / 1536 AS bytes_per_dim
FROM document_embedding;

-- This is matching a word, so it could have been done with 
-- same
-- The vector for "short" is near "tall".
-- The vector for "pretty" is near "beautiful".
-- The vector for "palace" is near "castle".
-- The vector for "chair" is near "sit".

SELECT MAX(inv_date) AS latest_invoice_date
FROM invoices;

CREATE TABLE invoice (
        order_id INTEGER PRIMARY KEY,
        product TEXT NOT NULL,
        amount NUMERIC(10,2),
        inv_date DATE
);

INSERT INTO invoice VALUES
        (735, 'gears', 21, '2024-04-12'),
        (4423, 'pencils', 13, '2024-02-05'),
        (1334, 'glue', 5, '2024-03-25');

