Overview

psql "host=<region>.sql.topk.io port=5432 user=topk password=<api-key> dbname=topk"

-- 1. Create a collection
CREATE TABLE books (
    title   TEXT NOT NULL,
    content TEXT          INDEX semantic_index(),
    author  TEXT NOT NULL,
    rating  FLOAT
);

-- 2. Insert documents
INSERT INTO books (_id, title, content, author, rating)
VALUES
    ('1', 'The Hobbit',
     'A hobbit embarks on an unexpected journey through Middle-earth.',
     'Tolkien', 4.3),
    ('2', '1984',
     'A dystopian novel about totalitarian surveillance and control.',
     'George Orwell', 4.7),
    ('3', 'Dune',
     'An epic saga of politics, religion, and ecology on a desert planet.',
     'Frank Herbert', 4.5);

-- 3. Search
SELECT _id, title, semantic_similarity(content, 'political control and oppression') AS score
FROM books
ORDER BY score DESC
LIMIT 10;

SELECT title, published_year::int4, rating::float8 FROM books LIMIT 10;

<table> ::= [schema.]collection[$partition]

SELECT <projection> FROM <table>
  [WHERE <filter>]
  [ORDER BY <expr> [ASC | DESC] LIMIT <n>]

WHERE (match('hobbit', title) OR match('dune', title))
  AND rating > 4.0

SELECT _id, title, bm25_score() AS score
FROM books
WHERE match('dune', title)
ORDER BY score DESC
LIMIT 10;

SELECT _id, title, bm25_score() AS score
FROM books
WHERE match('dune', title)
ORDER BY score DESC
LIMIT 10;

SELECT _id, title
FROM books
WHERE match_any(title, ARRAY['dune', 'hobbit'])
  AND rating > 4.0;

SELECT _id, title, bm25_score() AS score
FROM books
WHERE (match('hobbit', title) OR match('dune', title))
  AND rating > 4.0
ORDER BY score DESC
LIMIT 10;

-- Vector ANN
SELECT
    _id,
    title,
    vector_distance(embedding, '[1,0,0,0]'::f32_vector) AS vec_dist
FROM books
ORDER BY vec_dist DESC  -- DESC for cosine/dot_product; ASC for euclidean
LIMIT 3;

-- Sparse vector ANN
SELECT
    _id,
    title,
    vector_distance(sparse_emb, '{"0":1.0,"1":0.5}'::f32_sparse_vector) AS vec_dist
FROM books
ORDER BY vec_dist DESC
LIMIT 3;

-- Multi-vector MaxSim
SELECT
    _id,
    title,
    multi_vector_distance(multi_emb, '[[1.0,0.0,0.0,0.0]]'::f32_matrix) AS vec_dist
FROM books
ORDER BY vec_dist DESC
LIMIT 3;

-- Full-text BM25
SELECT
    _id,
    title,
    bm25_score() AS bm25_score
FROM books
WHERE match('hobbit rings', title)
ORDER BY bm25_score DESC
LIMIT 5;

-- Semantic similarity
SELECT
    _id,
    title,
    semantic_similarity(bio, 'tales of magic and adventure') AS sem_similarity
FROM books
ORDER BY sem_similarity DESC
LIMIT 3;

-- Hybrid: vector + boost
SELECT
    _id,
    title,
    vector_distance(embedding, '[1,0,0,0]'::f32_vector) AS vec_dist
FROM books
WHERE match_any(title, 'hobbit')
ORDER BY boost(vec_dist, in_print = true, 1.5)
LIMIT 5;

-- Boolean keyword predicates
SELECT _id, title
FROM books
WHERE match_all(title, 'the hobbit')
   OR match_any(title, ARRAY['dune', '1984'])
LIMIT 5;

INSERT INTO <table> (<col>, ...) VALUES (<val>, ...) [, (<val>, ...) ...];

INSERT INTO books (_id, title, author, published_year, rating, embedding, sparse_emb)
VALUES
    (
        'hobbit', 'The Hobbit', 'Tolkien', 1937, 4.3,
        '[1.0, 0.0, 0.0, 0.0]'::f32_vector,
        '{"0": 1.0, "1": 0.5}'::f32_sparse_vector
    );

UPDATE <table> SET <col> = <val> [, ...] WHERE _id = '<id>';
UPDATE <table> SET <col> = <val> [, ...] WHERE _id IN ('<id1>', '<id2>', ...);

DELETE FROM <table> WHERE _id = '<id>';
DELETE FROM <table> WHERE _id IN ('<id1>', '<id2>', ...);
DELETE FROM <table> WHERE <filter_expr>;
DELETE FROM <collection>$<partition>;

CREATE TABLE [IF NOT EXISTS] <table> (
    <column> <type> [NOT NULL] [INDEX <method>(<options>)],
    ...
);

CREATE TABLE books (
    title          TEXT NOT NULL               INDEX keyword_index(),
    author         TEXT NOT NULL,
    published_year INTEGER NOT NULL,
    rating         FLOAT,
    genre          TEXT,
    in_print       BOOLEAN,
    bio            TEXT                        INDEX semantic_index(),
    embedding      f32_vector(4)               INDEX vector_index(metric = 'cosine'),
    sparse_emb     f32_sparse_vector           INDEX vector_index(metric = 'dot_product'),
    multi_emb      f32_matrix(4)               INDEX multi_vector_index(metric = 'maxsim'),
    tags           TEXT[],
    checksum       BYTEA,
    metadata       JSONB
);

DROP TABLE [IF EXISTS] <table>;

SELECT table_name, table_schema, table_type FROM information_schema.tables;

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'books';

EXPLAIN <statement>;
EXPLAIN VERBOSE <statement>;

SELECT metadata.publisher, metadata.year FROM books LIMIT 10;

-- not supported
SELECT metadata FROM books;