Class PostgresFts

Postgres full-text search using tsvector columns and GIN indexes.

Unlike FTS5 (separate virtual table), Postgres stores the tsvector as a column _tsv on the content table itself. The createIndex() call adds the column, creates the GIN index, and backfills existing rows.

Implements

Constructors

constructor

Methods

createIndex matchClause rankExpression rebuildCommand syncInsert sanitizeQuery joinClause

Constructors

constructor

Methods

createIndex

  • createIndex(config): string

  • Generate DDL to create the full-text search index.

    Parameters

    • config: {
          table: string;
          columns: string[];
          contentTable?: string;
          tokenizer?: string;
      }
      • table: string

        Name for the FTS index/virtual table.

      • columns: string[]

        Columns to index.

      • OptionalcontentTable?: string

        Source table (for external-content FTS5).

      • Optionaltokenizer?: string

        Tokenizer config (e.g. 'porter ascii').

    Returns string

matchClause

  • matchClause(_indexName, queryPlaceholder): string

  • Generate a WHERE clause fragment for full-text matching. SQLite: memory_traces_fts MATCH ? Postgres: memory_traces._tsv @@ plainto_tsquery('english', $1)

    Parameters

    • _indexName: string
    • queryPlaceholder: string

    Returns string

rankExpression

  • rankExpression(_indexName, queryPlaceholder?): string

  • Generate an ORDER BY rank expression. SQLite: memory_traces_fts.rank Postgres: ts_rank(memory_traces._tsv, plainto_tsquery('english', $1))

    Parameters

    • _indexName: string
    • OptionalqueryPlaceholder: string

    Returns string

rebuildCommand

  • rebuildCommand(_indexName): string

  • Generate the rebuild/reindex command. SQLite: INSERT INTO fts_table(fts_table) VALUES('rebuild') Postgres: UPDATE content_table SET _tsv = to_tsvector('english', col1 || ' ' || col2)

    Parameters

    • _indexName: string

    Returns string

syncInsert

  • syncInsert(_indexName, _rowIdExpr, columns): string

  • Generate an INSERT to sync external-content FTS after a row insert. SQLite: INSERT INTO fts_table (rowid, col1, col2) VALUES (expr, ?, ?) Postgres: UPDATE content_table SET _tsv = to_tsvector(...) WHERE ...

    Parameters

    • _indexName: string
    • _rowIdExpr: string
    • columns: string[]

    Returns string

sanitizeQuery

  • sanitizeQuery(input): string

  • Sanitize natural-language input into a safe search query. SQLite: wraps words in quotes, strips FTS5 operators. Postgres: pass-through (plainto_tsquery handles it).

    Parameters

    • input: string

    Returns string

joinClause

  • joinClause(contentTable, contentAlias, _ftsAlias, _indexName): string

  • Generate a SELECT joining the FTS index to the content table. This handles the structural difference between FTS5 (separate virtual table joined via rowid) and Postgres (tsvector column on the content table itself).

    Parameters

    • contentTable: string

      The base table (e.g. 'memory_traces').

    • contentAlias: string

      Alias for the content table (e.g. 't').

    • _ftsAlias: string
    • _indexName: string

    Returns string

    FROM/JOIN clause fragment.

Settings

Member Visibility

On This Page

Constructors
Methods