Use synonyms-AnalyticDB(AnalyticDB)-阿里云帮助中心

The pgsearch extension on AnalyticDB for PostgreSQL lets you create synonym dictionaries to expand full-text search results. When a query matches a key in the dictionary, results include that key and all its configured synonyms — improving recall without requiring users to know the exact keyword.

Synonym dictionaries and vector search are complementary: synonyms cover lexical gaps (alternate names, industry jargon, multilingual equivalents), while vector search handles semantic similarity. Used together, they address different failure modes in search.

Workflow:

Create a synonym dictionary with a unique ID.
Populate it with term-to-synonyms mappings in jsonb format.
Query using pgsearch.synonym_terms() to automatically expand search terms.

Prerequisites

An AnalyticDB for PostgreSQL V7.0 instance running minor version V7.2.1.7 or later, or V7.3.2.2 or later.

Note

To check your minor version, see View the minor engine version. To upgrade, see Upgrade the minor engine version.

Manage synonym dictionaries

Create a synonym dictionary

Call pgsearch.synonyms_create() with a unique dictionary ID.

SELECT pgsearch.synonyms_create('<synonyms_id>');

Example

SELECT pgsearch.synonyms_create('warehouse');

Delete a synonym dictionary

Call pgsearch.synonyms_drop() with the dictionary ID.

SELECT pgsearch.synonyms_drop('<synonyms_id>');

Example

SELECT pgsearch.synonyms_drop('warehouse');

Add or update synonyms

Call pgsearch.synonyms_set() to insert or update synonym entries. The second argument is a jsonb object where each key is a term and the value is the list of synonyms for that term.

SELECT pgsearch.synonyms_set('<synonyms_id>', '<jsonb_data>');

Examples

-- Insert multiple synonym mappings
SELECT pgsearch.synonyms_set('warehouse',
    '{"pc": ["personal computer", "laptop"],
      "phone": ["telephone", "mobile"],
      "apple": ["mac", "iphone"],
      "shoes": ["socks", "boots"]}'
);

-- Overwrite the synonyms for a specific term
-- This replaces the existing synonyms (mac, iphone) with apples
SELECT pgsearch.synonyms_set('warehouse',
    '{"apple": ["apples"]}'
);

Query synonym dictionaries

List all dictionaries

SELECT * FROM pgsearch.synonyms_show();

Inspect a specific dictionary

-- List all terms in a dictionary, sorted alphabetically
SELECT * FROM pgsearch.synonyms_list('<synonyms_id>') ORDER BY word;

-- Look up synonyms for a specific term
SELECT * FROM pgsearch.synonyms_list('<synonyms_id>', '<word>');

Search with synonym dictionaries

Use pgsearch.synonym_terms() inside a query to expand a search term to include its synonyms. The function returns results that contain the queried term or any of its configured synonyms.

Function signature

pgsearch.synonym_terms(synonym_id => '<synonym_id>', field => '<field>', VALUE => '<value>')

Parameter	Description
`synonym_id`	The unique ID of the synonym dictionary
`field`	The column to search
`VALUE`	The term to look up

Basic example

The warehouse dictionary defines socks and boots as synonyms of shoes. The following query returns rows where the description column contains shoes, socks, or boots.

SELECT * FROM mock_items
WHERE description @@ pgsearch.config(
    query => pgsearch.synonym_terms(synonym_id => 'warehouse', field => 'description', VALUE => 'shoes')
);

Use synonym terms in advanced queries

pgsearch.synonym_terms() works inside Boolean search and set search expressions.

Boolean search example

Return rows where description contains book, shoes, or any synonym of shoes, but not speaker.

SELECT * FROM mock_items
ORDER BY description @@ pgsearch.config(
    query => pgsearch.boolean(
        should => ARRAY[
            pgsearch.term('description:book'),
            pgsearch.synonym_terms(synonym_id => 'warehouse', field => 'description', VALUE => 'shoes')
        ],
        must_not => ARRAY[
            pgsearch.term(field => 'description', VALUE => 'speaker')
        ]
    )
);

Set search example

Return rows where description contains book, shoes, or any synonym of shoes.

SELECT * FROM mock_items
WHERE description @@ pgsearch.config(
    query => pgsearch.term_set(
        terms => ARRAY[
            pgsearch.synonym_terms(synonym_id => 'warehouse', field => 'description', VALUE => 'shoes'),
            pgsearch.term(field => 'description', VALUE => 'book')
        ]
    )
);

For a full reference on Boolean search and set search, see the Usage guide.

What's next

Combine synonym dictionaries with vector search to improve both lexical and semantic recall.
See the Usage guide for a full reference on Boolean search, set search, and other advanced query patterns.