Database Viewer

CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  -- [databases, web, AI]: how much the document is about each topic.
  embedding DOUBLE PRECISION[] NOT NULL
);


INSERT INTO documents (title, body, embedding)
VALUES
  ('SQL indexes', 'Indexes help PostgreSQL find rows without reading the whole table.', ARRAY[0.95, 0.03, 0.02]),
  ('JOIN patterns', 'Joins combine rows from related tables into one result.', ARRAY[0.66, 0.17, 0.17]),
  ('Query tuning', 'Slow queries get faster with the right index and a better plan.', ARRAY[0.62, 0.15, 0.23]),
  ('React components', 'Components split a user interface into small reusable pieces.', ARRAY[0.25, 0.26, 0.49]),
  ('CSS grid layouts', 'Grid and flexbox position elements on a web page.', ARRAY[0.15, 0.82, 0.03]),
  ('Neural networks', 'Neural networks learn patterns from training data.', ARRAY[0.14, 0.08, 0.78]),
  ('Chatbots', 'Language models answer questions written in natural language.', ARRAY[0.32, 0.26, 0.42]),
  ('Vector search', 'Embeddings let a database find documents with a similar meaning.', ARRAY[0.91, 0.04, 0.04]);


-- unnest(a, b) zips two arrays into rows of (x, y) pairs,
-- so these work for any number of dimensions.

-- Euclidean (L2) distance: the straight-line distance between two points.
CREATE FUNCTION l2_distance(a DOUBLE PRECISION[], b DOUBLE PRECISION[])
RETURNS DOUBLE PRECISION
LANGUAGE sql IMMUTABLE
AS $$
  SELECT sqrt(sum((x - y) ^ 2)) FROM unnest(a, b) AS t(x, y)
$$;

-- Cosine similarity: 1 when two vectors point the same way, 0 when unrelated.
CREATE FUNCTION cosine_similarity(a DOUBLE PRECISION[], b DOUBLE PRECISION[])
RETURNS DOUBLE PRECISION
LANGUAGE sql IMMUTABLE
AS $$
  SELECT sum(x * y) / (sqrt(sum(x * x)) * sqrt(sum(y * y)))
  FROM unnest(a, b) AS t(x, y)
$$;