CREATE EXTENSION vector error: type does not exist, but Server Parameter enabled

Question

I've got a Postgresql - Flexible Server database deployed, and I'm trying to enable the pgvector extension. I've activated it from Server Parameters > azure.extensions > [select VECTOR] and save.

I can see the extension is available, but I get the error type "vector" does not exist when I try to execute CREATE EXTENSION vector;

I can see the extension is available, but I get the error type "vector" does not exist when I try to execute CREATE EXTENSION vector;

I'm running this as the admin user.

It's weird because I've got a similar setup in another staging instance, and I was able to execute/create the extension just fine there.

Answer 1

Hi @Tim Law

Please check the below detailed information on how "vector" extension works in Azure :-

https://gist.github.com/sageTimNewton/f20583ff1f0cf0af648bc0a0a91c0083

May I please know What version of Azure PostgreSQL flexible server are you using?

In summary, based on the above documentation, you may need to upgrade the instance to version 15 (preview) of PostgreSQL and enable the "Vector" extension under the azure.extensions server parameter.

Microsoft reference document - https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-use-pgvector

Please verify if these instructions are applicable to your specific scenario.

Thank you.

Answer 2

Found the issue: a little obscure but in case anyone runs into this issue in the future -- my db admin user name was the same as a schema I had created. This had consequences as the search_path is set to $user, public and when I tried to run CREATE EXTENSION vector it was looking at that same-named schema, which...seemed to be causing the "does not exist" error. If I change the command to be explicit about the schema, i.e. CREATE EXTENSION vector WITH SCHEMA public then it worked.