Analyze text sentiment

The ai.analyze_sentiment function is a built-in AlloyDB AI tool that classifies the sentiment of text as positive, negative, or neutral. 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 sentiment analysis function provides the following benefits:

World knowledge: the vast collection of facts, concepts, relationships, and contextual understanding of the world that Large Language Models (LLM) acquire during their pre-training phase.
Real-time analysis: brings Gemini's world knowledge to enterprise data using SQL.
Scalability: 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 sentiment analysis function supports several use cases, including the following:

Customer feedback: classify thousands of raw, unstructured product reviews to identify customer satisfaction levels.
Social media monitoring: analyze sentiment on social mentions or comments to gauge public perception of a brand.

Before you begin

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

Enable the extension

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

To enable the google_ml_integration.enable_preview_ai_functions flag in AlloyDB, you use the SET command. This flag controls access to preview AI functions like ai.analyze_sentiment.

Make sure that your google_ml_integration extension is version 1.5.7 or higher. You can check the version by running the following:
```
SELECT extversion FROM pg_extension WHERE extname = 'google_ml_integration';
```
If you need to upgrade to a version that includes these preview functions, call the following:
```
CALL google_ml.upgrade_to_preview_version();
```
Enable the flag for the current session or for the entire database. To enable the flag for your current session, execute the following:
```
SET google_ml_integration.enable_preview_ai_functions = 'on';
```
This change doesn't require a database restart. The default value of this flag is off.

Create an example table

To follow the analyze sentiment function examples in this document, create a table and populate it with the following movie reviews.

CREATE TABLE IF NOT EXISTS reviews (
    id INT PRIMARY KEY,
    review_content TEXT
);

INSERT INTO reviews (id, review_content) VALUES 
(1, 'This movie is very good'),
(2, 'The actors play the parts well'),
(3, 'I like the music in this film'),
(4, 'The story is easy to follow'),
(5, 'Many people will enjoy this show'),
(6, 'The film is too long'),
(7, 'I do not like the ending'),
(8, 'This movie is very boring'),
(9, 'The story is okay'),
(10, 'Some parts are fine');

Analyze sentiment of a single string

To evaluate the sentiment of a single text input, use the scalar version of ai.analyze_sentiment.

SELECT ai.analyze_sentiment(
  prompt => 'TEXT_CONTENT',
  model_id => 'MODEL_ID' -- Optional. The default value is gemini-2.5-flash-lite.
);

The following example shows you how to perform row-level sentiment analysis on text data stored in a table named reviews with columns id and review_content to store review data. The example executes a SELECT query that applies the ai.analyze_sentiment() function to the review_content column for each row in the table. This function processes each review individually and returns its calculated sentiment (positive, negative, or neutral).

--- Row Level sentiment analysis
SELECT ai.analyze_sentiment(review_content) FROM reviews;

The following is the example output:

 id | analyze_sentiment
----+-------------------
  1 | positive
  2 | positive
  3 | positive
  4 | positive
  5 | positive
  6 | negative
  7 | negative
  8 | negative
  9 | neutral
 10 | neutral

Analyze sentiment in batches

For better performance on larger datasets, use the array-based version of the function to process multiple strings in a single call.

SELECT ai.analyze_sentiment(
  prompts => ARRAY['TEXT_1', 'TEXT_2'],
  batch_size => BATCH_SIZE, -- Optional. The default value is 10.
  model_id => 'MODEL_ID' -- Optional. The default value is gemini-2.5-flash-lite.
);

The following example analyzes the sentiment of customer reviews from a table named reviews.

WITH sentiment_results AS (
SELECT
  ARRAY_AGG(id ORDER BY id) as ids,
  ai.analyze_sentiment( 
    prompts => array_agg( 'Please analyze the sentiment of this review  : ' || review_content
      ORDER BY id),
    batch_size => 15) as sentiments 
FROM reviews
),
correlated_results AS (
    SELECT ids[i] as id, sentiments[i] as sentiment
    FROM sentiment_results,
    generate_series(1, array_length(ids, 1)) AS i
)
SELECT reviews.id, correlated_results.sentiment as sentiment 
FROM reviews
JOIN correlated_results ON reviews.id = correlated_results.id
ORDER BY reviews.id DESC;

The following is the example output:

 id | sentiment
----+-----------
  1 | positive
  2 | positive
  3 | positive
  4 | positive
  5 | positive
  6 | negative
  7 | negative
  8 | negative
  9 | neutral
 10 | neutral

Analyze sentiment using cursors

The cursor-based function is intended for processing large datasets efficiently, because it lets the system stream data through the AI model in manageable batches without requiring all of the data to be loaded into memory at once.

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

CREATE OR REPLACE FUNCTION ai.analyze_sentiment(
    prompt TEXT,
    input_cursor REFCURSOR,
    batch_size INT DEFAULT NULL,
    model_id VARCHAR(100) DEFAULT NULL)
  RETURNS REFCURSOR

You can now use the ai.analyze_sentiment function. Since it expects a REFCURSOR, you need to open a cursor for the input data that you want to analyze. In this example, you analyze the review_content from the reviews table.

The following example shows how to feed data to the ai.analyze_sentiment function row by row using a cursor:

-- Start a transaction
BEGIN;

-- Declare a cursor for the review content
DECLARE review_cursor REFCURSOR;

-- Open the cursor with the query to fetch the review content
OPEN review_cursor FOR SELECT review_content FROM reviews;

-- Call the AI function, passing the cursor
-- This function will return another cursor containing the results
DECLARE result_cursor REFCURSOR;
SELECT ai.analyze_sentiment(
    prompt => 'Analyze the sentiment of the following movie review:',
    input_cursor => review_cursor,
    batch_size => 5  -- Optional: Process in batches of 5
) INTO result_cursor;

-- Fetch and display results from the result_cursor
-- The exact way to fetch from a REFCURSOR depends on the SQL environment.
-- This is a conceptual example.
FETCH ALL FROM result_cursor;

-- Close the cursors
CLOSE review_cursor;
CLOSE result_cursor;

-- End the transaction
COMMIT;

The following is the output:

      review_content            | sentiment | score
------------------------------+-----------+-------
This movie is very good        | Positive  |   0.9
The actors play the parts well | Positive  |   0.8
I like the music in this film  | Positive  |   0.8
The story is easy to follow    | Positive  |   0.7
Many people will enjoy this show | Positive  |   0.8
The film is too long           | Negative  |  -0.6
I do not like the ending       | Negative  |  -0.8
This movie is very boring      | Negative  |  -0.9
The story is okay              | Neutral   |   0.1
Some parts are fine            | Neutral   |   0.2

What's next

Summarize content.
Read the AI functions overview.