Type "vector" does not exist on postgresql - langchain

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.

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.

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.

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 
CREATE EXTENSION vector;

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

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;