Create a vector table for vector search - AnalyticDB

Vector tables in AnalyticDB for PostgreSQL use the same CREATE TABLE syntax as heap tables. The vector column stores embeddings as an array and supports three data types. The syntax is fully compatible with SQL:1999 and partially compatible with SQL:2003.

Prerequisites

Before you begin, ensure that you have:

Enabled vector search engine optimization. For more information, see Enable or disable vector search engine optimization.

Syntax

CREATE TABLE [TABLE_NAME]
(
    C1 DATATYPE,
    C2 DATATYPE,
    ......,
    CN VECTORTYPE,
    PRIMARY KEY (one or more columns)
) DISTRIBUTED BY (C1);

VECTORTYPE is a placeholder for the vector column data type. The vector column supports the following types: SMALLINT[], FLOAT2[], and REAL(FLOAT4)[]. A table can have multiple vector columns.

Example

The following example creates a table named FACE_TABLE that stores face recognition embeddings alongside metadata:

CREATE TABLE FACE_TABLE (
    C1 INT,
    C2 REAL[] NOT NULL,
    C3 TIMESTAMP NOT NULL,
    C4 VARCHAR(20) NOT NULL,
    PRIMARY KEY (C1)
) DISTRIBUTED BY (C1);

Column	Type	Description
`C1`	`INT`	Primary key and distribution key
`C2`	`REAL[]`	Vector column storing face embeddings
`C3`	`TIMESTAMP`	Timestamp column
`C4`	`VARCHAR(20)`	Label or metadata string associated with the embedding

Next steps

After creating a vector table, create a vector index to enable fast similarity search.