Problem about getting the distance ,using pgvector.psycopg2

[sky92archangel]

Here is my data in table called items :

ID: 1,  embedding: [1. 2. 3.]
ID: 2,  embedding: [4. 5. 6.]
ID: 3,  embedding: [ 11. 233.  48.]
ID: 4,  embedding: [ 11. 233.  48.]
ID: 5,  embedding: [ 14.  22. 414.]
ID: 1,  embedding: [1. 2. 3.]
ID: 2,  embedding: [4. 5. 6.]
ID: 3,  embedding: [ 11. 233.  48.]
ID: 4,  embedding: [ 11. 233.  48.]
ID: 5,  embedding: [ 14.  22. 414.]

code:

import psycopg2
# from pgvector.psycopg import register_vector
from pgvector.psycopg2  import register_vector

# CONN
DB_HOST = "192.168.35.131"
DB_PORT = "5432"
DB_NAME = "mydatabase"
DB_USER = "postgres"
DB_PASSWORD = "postgres"

def connect_db():
    """connect to PostgreSQL  """
    conn = psycopg2.connect(
        host=DB_HOST,
        port=DB_PORT,
        dbname=DB_NAME,
        user=DB_USER,
        password=DB_PASSWORD
    )
    return conn

def query_similar_vectors(query_vector):
    """MOST similarity"""
    conn = connect_db()
    cursor = conn.cursor()
    register_vector(conn)

    # query for cloest
    cursor.execute('''SELECT id, 1-(embedding <-> %s) AS similarity
        FROM items 
        ORDER BY embedding <-> %s 
        LIMIT 5''', (query_vector,))
    # cursor.execute("""
    #     SELECT  embedding, 1 - (embedding <=> %s) AS similarity
    #     FROM items
    #     ORDER BY embedding <=> %s
    #     LIMIT 5;
    # """, (query_vector,))

    rows = cursor.fetchall()
    for row in rows:
        id, similarity = row
        print(f"ID: {id}, INFO: {similarity}")

    cursor.close()
    conn.close()

# DEMO：query for cloest
query_vector = np.random.rand(3).astype(np.float32)
print(query_vector);
query_similar_vectors(query_vector)

run the code ,then get error :

---------------------------------------------------------------------------
IndexError                                Traceback (most recent call last)
Cell In[44], line 30
     28 query_vector = np.random.rand(3).astype(np.float32)
     29 print(query_vector);
---> 30 query_similar_vectors(query_vector)

Cell In[44], line 8, in query_similar_vectors(query_vector)
      5 register_vector(conn)
      7 # 查询最相似的向量
----> 8 cursor.execute('''SELECT id, 1-(embedding <-> %s) AS similarity
      9     FROM items 
     10     ORDER BY embedding <-> %s 
     11     LIMIT 5''', (query_vector,))
     12 # cursor.execute("""
     13 #     SELECT  embedding, 1 - (embedding <=> %s) AS similarity
     14 #     FROM items
     15 #     ORDER BY embedding <=> %s
     16 #     LIMIT 5;
     17 # """, (query_vector,))
     19 rows = cursor.fetchall()

IndexError: tuple index out of range

if I change the query string to

SELECT *
        FROM items 
        ORDER BY embedding <-> %s 
        LIMIT 5

it will be fine ,and return the result :

[0.3383062  0.60955006 0.5269664 ]
ID: 7, INFO: [0.8093753  0.17524134 0.1668515 ]
ID: 1, INFO: [1. 2. 3.]
ID: 2, INFO: [4. 5. 6.]
ID: 6, INFO: [2.3584 9.39   6.004 ]
ID: 4, INFO: [ 11. 233.  48.]

so I guess the problem occur at "SELECT 1-(embedding <-> %s) FROM items";
SO, WHAT'S HAPPEND ABOUT THE FEATURE ?

[ankane]

Hi @sky92archangel, if you have two %s values in the query, you need to pass two parameters. See the Psycopg 2 docs for more info (this isn’t specific to pgvector).