Type "vector" does not exist on postgresql – langchain – Postgresql

The Problem:

A user is attempting to create a table in a PostgreSQL database using the ‘vector’ data type provided by the pgvector extension. However, upon execution, they encounter the error ‘type "vector" does not exist,’ indicating that the extension is not properly recognized by the database.

The Solutions:

Solution 1: Update 17th July 2023

The issue may be due to one of the following reasons:

The pgvector extension is not enabled in the database you are using. Run CREATE EXTENSION vector; in each database you are using for storing vectors.

The vector schema is not in the search_path. Run SHOW search_path; to see the available schemas in the search path and \dx to see the list of installed extensions with schemas.

Solution 2: Initializing Extension with Docker

To resolve the issue, run the init.sql script during Docker container initialization to create the vector extension. This ensures that the extension is available when the application starts.

In the Docker Compose configuration, add the following volume mapping to mount the init.sql script into the container:

volumes:
  - ./init.sql:/docker-entrypoint-initdb.d/init.sql

Create the init.sql script with the following content:

CREATE EXTENSION vector;

When you build the Docker image, the init.sql script will be executed automatically, creating the vector extension. This should resolve the error.

Solution 3: Re-create tables in the correct schema

Langchain uses two tables: `langchain_pg_collection` (plain table) and `langchain_pg_embedding` (has a vector column). When configuring the application, ensure both tables are created in the same schema, as mixing schemas can cause the “type vector does not exist” error.

To resolve this:

1. Drop both tables from your schema and public.
2. Restart the application to recreate the tables correctly.

Solution 4: Set Permanent Search Path

To resolve the issue, set a permanent search path for the database. Run the following command:

ALTER DATABASE postgres SET SEARCH_PATH TO postgres_schema;

Here, “postgres” is the current database and “postgres_schema” is the schema to set as the search path. This command sets the search path at the database level permanently, ensuring that langchain can access the required data types.

Video Explanation: