Error when creating vector db (v3.14.1)

I can't create any vector db. I get this regardless of the type (Document/Site URLs).
Immagine 2023-10-09 141537

Hi @theidleguest. I am an engineer on the Retool team. This error usually means that you do not have adequate permissions to create tables in Retool Database (the underlying datastore for Retool Vectors). Can you try running this command via your Retool Database connection string: CREATE TABLE ${tableName} (id serial PRIMARY KEY, embedding vector(1536)); This will also verify that you have pgvector installed, which is necessary to use Retool Vectors.

Let me know if you need any help finding your Retool Database connection string (it's at the top left of the Retool Database screen).

Hello @francis12 ,
I can get to the Retool Database connection string panel. I can't trigger any query from there, though...
Immagine 2023-10-10 105202

Actually, I found out I could use the Query Library for that.

I run the command and the response was

  • error:"type modifier is not allowed for type "vector""

I installed pgvector as per GitHub - pgvector/pgvector: Open-source vector similarity search for Postgres
If I got it right, to enable the extension in Postgres I have to run the command CREATE EXTENSION vector;.
If I use the command line, the problem is I don't have root access to Postgress to perform the command.
If I use the Query Library, I get:

Hi @theidleguest, you're doing everything correctly, but here a few things to potentially verify/try.

  1. Make sure that the pgvector version you installed is compatible with your version of Postgres
  2. Run all your terminal commands with the sudo prefix
  3. You are using the correct GitHub to install pgvector, but perhaps just go through the steps again and make sure you are not missing anything.

Hello @francis12 , thank you.
I think I am pretty much stuck at the moment.
I am using the latest Docker image of Retool on premise (3.14.2).

I have some questions regarding the postgres containers.

  1. I see there are two containers for Postgres. What's the difference? Which is doing what? One is using Postgres 11, the other Postgres 14

  1. Should the pgvector be installed by accessing either of the two via Terminal?

It'd very helpful for the community if you could publish a quick how-to guide for this, as I think many will stumble on the same issue :wink:

Hi @theidleguest, install pgvector on retooldb-postgres-1. That's what we use for Retool Vectors.

Thanks for the feedback as well! We will investigate how we can make things clearer.

Hello @francis12 ,
I am still stuck. I have a question: what is the command that is sent when I click on "Create"? Is it CREATE EXTENSION pgvector? If so, wouldn't it be CREATE EXTENSION vector, instead?

Solution found. Entered in my Retool db as root (credentials in retooldb.env) and installed pgvector via CREATE EXTENSION vector.

1 Like