Classify text

The ai.classify function is a built-in AlloyDB AI tool that categorizes text inputs into a set of classes or labels that you define. By embedding this capability directly into the database, AlloyDB for PostgreSQL lets you process unstructured data without building complex Extract, Transform, and Load (ETL) pipelines or external service integrations.

The text classification function provides the following benefits:

Real-time classification: brings Gemini's world knowledge to enterprise data using SQL.
Scalability: supports smart batching for AI functions—also referred to as AI function acceleration—which is an automatic query execution optimization feature in AlloyDB AI that transparently groups individual, row-by-row scalar AI function calls into batched model requests in the background during query execution. Also supports array-based and cursor-based processing to handle thousands of rows efficiently.
Simplicity: provides a high-level abstraction that manages model invocation and data preparation automatically.

AlloyDB AI's text classification function supports several use cases, including the following:

Topic Classification: assign predefined topics (such as sports, technology, or finance) to documents or articles.
Spam Detection: identify incoming emails, comments, or messages as spam.
Intent Recognition: determine the user's goal behind a query, such as booking a flight, checking the weather, or ordering food.

Before you begin

Make sure that you meet the following requirements before you use the ai.classify function:

PostgreSQL version: The ai.classify function is supported on PostgreSQL version 18 and later. Make sure that your AlloyDB cluster is running PostgreSQL 18 or later. For more information, see Supported database versions.
Extension version: Make sure that you have the latest version of the google_ml_integration extension (version 1.6.1 or higher) installed with Preview functionality enabled.

Enable the extension

Make sure that you have the latest version of the google_ml_integration extension (version 1.6.1 or higher) installed with Preview functionality enabled.

To enable the google_ml_integration.enable_ai_query_engine and google_ml_integration.enable_preview_ai_functions flags in AlloyDB, you use the SET command. These flags control access to the AI query engine and preview AI functions like ai.classify.

Make sure that your google_ml_integration extension is version 1.6.1 or higher. To verify the version, run the following:
```
SELECT extversion FROM pg_extension WHERE extname = 'google_ml_integration';
```
Enable the flags for the current session or for the entire database. To enable the flags for your current session, run the following:
```
SET google_ml_integration.enable_ai_query_engine = 'on';
SET google_ml_integration.enable_preview_ai_functions = 'on';
```
This change doesn't require a database restart. The default value of these flags is off.

Create an example table

To follow the classify function examples in this document, create a table and populate it with the following sample product reviews.

CREATE TABLE IF NOT EXISTS product_reviews (
    id INT PRIMARY KEY,
    review TEXT
);

INSERT INTO product_reviews (id, review) VALUES
(1, 'Fantastic, would buy again!'),
(2, 'Broken on arrival.'),
(3, 'Mediocre, not worth the price.');

Classify a single string

To categorize a single text input, use the scalar version of ai.classify. For detailed API documentation, see the ai.classify reference.

SELECT ai.classify(
  input => 'TEXT_CONTENT',
  categories => ARRAY['CATEGORY1', 'CATEGORY2', 'CATEGORY3'],
  examples => 'EXAMPLES', -- Optional
  output_mode => 'OUTPUT_MODE', -- Optional. Can be 'single' or 'multi'.
  model_id => 'MODEL_ID' -- Optional. The default value is gemini-3.5-flash-lite.
);

Classify text into mutually exclusive categories

The following example shows a basic use case of classifying a single piece of text into one of several mutually exclusive categories:

SELECT ai.classify(
  input => 'This is a sales report for Q1.',
  categories => ARRAY['Report', 'Invoice', 'Memo']
);

The following is the example output:

Report

Classify multi-label topic

The following example classifies a news headline into multiple topics by setting output_mode to 'multi' and by providing category descriptions:

SELECT ai.classify(
  input => 'The government announced new regulations on AI development, citing both economic opportunities and potential security risks.',
  categories => ARRAY[
    ARRAY['politics', 'Questions about politics'],
    ARRAY['technology', 'Questions about technology'],
    ARRAY['security', 'Questions about security']
  ],
  output_mode => 'multi'
);

The following is the example output:

politics,technology,security

Use categories with descriptions

The following example demonstrates the preferred way of providing categories using a 2D array format with labels and descriptions for better clarity:

SELECT ai.classify(
  input => 'The patient reported mild fever and headache.',
  categories => ARRAY[
    ARRAY['urgent', 'Requires immediate medical attention'],
    ARRAY['non-urgent', 'Routine check-up or minor symptoms'],
    ARRAY['query', 'A question about services or appointments']
  ]
);

The following is the example output:

non-urgent

Provide few-shot examples

The following example shows how to guide the model's classification by providing examples of inputs and their expected outputs:

SELECT ai.classify(
  input => 'Is this item returnable after 90 days?',
  categories => ARRAY[
    ARRAY['policy', 'Questions about return, warranty, or other policies'],
    ARRAY['shipping', 'Questions about order shipment and delivery'],
    ARRAY['payment', 'Questions about payment methods or issues'],
    ARRAY['other', 'Any other type of question']
  ],
  examples => '[
    {"input": "When will my order arrive?", "output": "shipping"},
    {"input": "What is the warranty on this?", "output": "policy"},
    {"input": "My discount code isn''t working.", "output": "payment"}
  ]'
);

The following is the example output:

policy

Use a specific model ID

The following example demonstrates how to use a specific model version or type by referencing its ID:

SELECT ai.classify(
  input => 'This new song is a banger!',
  categories => ARRAY[
    ARRAY['positive', 'Expresses approval or enjoyment'],
    ARRAY['negative', 'Expresses disapproval or dislike']
  ],
  model_id => 'MODEL_ID'
);

The following is the example output:

positive

Replace MODEL_ID with the ID of a supported pre-registered model or a custom model that you registered. For a list of supported pre-registered models, see Supported AI models. For instructions on registering other model endpoints, see Register and call remote AI models using model endpoint management.

Classify in batches

When you classify large datasets across multiple rows, you can optimize performance using smart batching for AI functions or by using the array-based version of ai.classify.

Smart batching

To accelerate queries that run scalar ai.classify across multiple rows without writing complex array aggregation queries, enable smart batching for AI functions by setting the google_ml_integration.enable_ai_function_acceleration database flag to on:

SET google_ml_integration.enable_ai_function_acceleration = 'on';

When this flag is enabled, AlloyDB automatically groups individual scalar ai.classify calls into smart batches in the background.

SELECT
  id,
  ai.classify(
    input => review,
    categories => ARRAY['positive', 'neutral', 'negative']
  ) AS sentiment
FROM product_reviews
ORDER BY id;

Array-based batching

For explicit control over batch processing on larger datasets, use the array-based version of ai.classify to process multiple inputs at once by passing a SQL array of inputs. For detailed API documentation, see the ai.classify reference.

SELECT ai.classify(
  inputs => TEXT[],
  categories => TEXT[][],
  examples => TEXT, -- Optional.
  output_mode => VARCHAR, -- Optional. The default value is single.
  batch_size => INT, -- Optional. The default value is 10.
  model_id => VARCHAR(100) -- Optional. The default value is gemini-3.5-flash-lite.
);

The following example classifies multiple reviews at once:

SELECT unnest(
  ai.classify(
    inputs => ARRAY(SELECT review FROM product_reviews ORDER BY id),
    categories => ARRAY[
      ARRAY['positive', 'Good sentiment'],
      ARRAY['neutral',  'Neither good nor bad sentiment'],
      ARRAY['negative', 'Bad sentiment']
    ],
    batch_size => 5
  )
) AS sentiment;

The following is the example output:

 sentiment
-----------
 positive
 negative
 negative

Classify using cursors

The cursor-based function processes large datasets efficiently by letting the system stream data through the AI model in manageable batches without requiring all of the data to be loaded into memory at once. For more information, see the ai.classify reference.

The function signature for the cursor-based version of ai.classify is as follows:

CREATE OR REPLACE FUNCTION ai.classify(
    input_cursor REFCURSOR,
    categories TEXT[][],
    examples TEXT DEFAULT NULL,
    output_mode VARCHAR DEFAULT NULL,
    batch_size INT DEFAULT NULL,
    model_id VARCHAR(100) DEFAULT NULL)
  RETURNS REFCURSOR

The following example shows how to use a cursor to feed inputs to ai.classify:

DO $$
DECLARE
  cur REFCURSOR;
  result_cur REFCURSOR;
  input_val TEXT;
  sentiment TEXT;
BEGIN
  OPEN cur FOR SELECT review FROM product_reviews ORDER BY id;

  result_cur := ai.classify(
    input_cursor => cur,
    categories => ARRAY[
      ARRAY['positive', 'Good sentiment'],
      ARRAY['neutral',  'Neither good nor bad sentiment'],
      ARRAY['negative', 'Bad sentiment']
    ],
    batch_size => 20
  );

  LOOP
    FETCH result_cur INTO input_val, sentiment;
    EXIT WHEN NOT FOUND;
    RAISE NOTICE 'Sentiment: %', sentiment;
  END LOOP;

  CLOSE result_cur;
  CLOSE cur;
END $$;

What's next

Extract structured data.
Read the AI functions overview.

Classify text Stay organized with collections Save and categorize content based on your preferences.

Before you begin

Enable the extension

Create an example table

Classify a single string

Classify text into mutually exclusive categories

Classify multi-label topic

Use categories with descriptions

Provide few-shot examples

Use a specific model ID

Classify in batches

Smart batching

Array-based batching

Classify using cursors

What's next

Classify text