Type "vector" does not exist on postgresql - langchain

Question

I was trying to embed some documents on postgresql with the help of pgvector extension and langchain. Unfortunately I'm having trouble with the following error:

(psycopg2.errors.UndefinedObject) type "vector" does not exist
LINE 4:  embedding VECTOR(1536), 
                   ^

[SQL: 
CREATE TABLE langchain_pg_embedding (
    collection_id UUID, 
    embedding VECTOR(1536), 
    document VARCHAR, 
    cmetadata JSON, 
    custom_id VARCHAR, 
    uuid UUID NOT NULL, 
    PRIMARY KEY (uuid), 
    FOREIGN KEY(collection_id) REFERENCES langchain_pg_collection (uuid) ON DELETE CASCADE
)
]

---

My environment info:

• pgvector docker image ankane/pgvector:v0.4.1
• python 3.10.6, psycopg2 2.9.6, pgvector 0.1.6

---

List of installed extensions on postgres

  Name   | Version |   Schema   |                Description                 
---------+---------+------------+--------------------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
 vector  | 0.4.1   | public     | vector data type and ivfflat access method

I've tried the following ways to resolve:

1. Fresh installing the Postgres docker image with pgvector extension enabled.
2. Manually install the extension with the official instruction.
3. Manually install the extension on Postgres like the following:

CREATE EXTENSION IF NOT EXISTS vector
    SCHEMA public
    VERSION "0.4.1";

But no luck.

Eastbourne

Try writing vector data type in lowercase that custom type maybe case sensitive. – Schoenfelder

Thanks, but no luck :( (updated the post with my tried ways @PepeNO – Eastbourne

psycopg2 is outdated try psycopg3 psycopg.org/psycopg3/docs/basic/install.html – Schoenfelder

Is the schema "public" in your search_path when you do the create? If it is not, that completely explains the error. – Battlement

I got "$user", public in search path after running SHOW search_path; @Battlement – Eastbourne

thanks, @PepeNO, I've figured it out, the issue was with something else. It was my environment variables issue which was falling back to the default database table which doesn't have the extension enabled. God that's frustrating. The extension was totally okay with my defined database. – Eastbourne

Answer 1

Update 17th July 2023

As previously I mentioned my issue was somewhere else in my configuration, here is the other reason that may be responsible for the error,

1. The pgvector extension isn't enabled in the database you are using. Make sure you run CREATE EXTENSION vector; in each database you are using for storing vectors.
2. 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.

---

Unfortunately, the issue was somewhere else. My extension installation and search_path schema were totally okay for the defined database I was supposed to use. But my environment variable which was responsible for which database to use, got messed up and was using the default database postgres instead of my defined database, which didn't have the extension enabled.

Eastbourne

Answer 2

I solved a similar problem by running init.sql with create extension when building the docker container.

The snippet of the docker-compose

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

init.sql

CREATE EXTENSION vector;

Dover

it not work for me – Watkins

event, I accessed to postgresql container and ran create extension vector; here's error: ERROR: extension "vector" is not available DETAIL: Could not open extension control file "/usr/share/postgresql/16/extension/vector.control": No such file or directory. HINT: The extension must first be installed on the system where PostgreSQL is running. – Watkins

Answer 3

I have also encountered such an issue when I directly use psycopg2 to connect to the database and execute the following SQL statement:

cur.execute('''
CREATE TABLE langchain_pg_embedding (
    uuid UUID NOT NULL,
    collection_id UUID,
    embedding VECTOR,
    document VARCHAR,
    cmetadata JSON,
    custom_id VARCHAR,
    PRIMARY KEY (uuid))
''')

There is no issue executing this database statement successfully. However, when I use langchain, I encounter an error stating that the data type does not exist.

Just now, I resolved this issue by setting a permanent search path for the database.

ALTER DATABASE postgres SET SEARCH_PATH TO postgres_schema;

• Here, “postgres” is the name of the current database.
• The “postgres_schema” represents the schema to be set as the search path.
• The above command will change the schema search path at the database level, permanently.

Menander

Answer 4

I resolved the issue by follow the following steps:

cd /tmp
git clone --branch v0.4.4 https://github.com/pgvector/pgvector.git
cd pgvector 
make
sudo make install 

Run this in PSQL Tool:

CREATE EXTENSION vector;

Cookson

Answer 5

Langchain uses two tables and only one uses VECTOR. While configuring the application, if one table gets created in one schema and the other is getting created in another schema, that will cause this error as well.

Just delete(move :) ) the langchain tables from your schema and public and then retry starting the application again after the settings stabilized. Then the tables should be created correctly.

• langchain_pg_collection - plain table
• langchain_pg_embedding - has a vector column, is created second and has a foreign key to langchain_pg_embedding_collection_id_fkey

Vardar

Answer 6

Without touching compose.yml and using SQLAlchemy, somewhere in python code during app initialization:

from sqlalchemy import create_engine
from sqlalchemy.orm import Session

engine = create_engine("postgresql+psycopg2://user:password@localhost/")

with Session(engine) as session:
    with session.begin():
        session.execute(text("create extension if not exists vector;"))

Indefectible