support type casts in query typings

[mosnicholas]

Describe the bug
Embeddings type definition has been updated to string -- it is represented internally as a vector, and in code often as a number[] -- I think number[] would be a more apt definition of the type in typescript.

[ChuckJonas]

This used to work... Trying to track down the change but can't seem to find it... Pretty frustrating that you haven't even got a response on a breaking change like this...

@codesnik any thoughts?

[sweatybridge]

The change should be in postgres-meta repo. @soedirgo do we need a new patch release for pgmeta?

[ChuckJonas]

here is the PR: supabase/postgres-meta#559

I've been creating records and calling rpc using number[], but did just check the result of a query and it's a string so I don't really get what's going on.

[soedirgo]

As mentioned in the PR, vector is represented as a JSON string in the response, so we type it as string. You'd need to do a cast like embedding::_float4, though the query typings don't support this atm.

[soedirgo]

Converting this issue to supporting casts in query typings

[codesnik]

@codesnik any thoughts?

@ChuckJonas you probably wanted to mention someone else?

[ChuckJonas]

@codesnik ya sorry!

For some reason, when I typed @soedirgo github shows your username as the first option 🤷

[ChuckJonas]

@soedirgo do you have any idea why it actually still works to pass an array of numbers to the supabase client? I'm guessing maybe the supabase client calls JSON.stringify if it receives a javascript object?

It's fairly annoying to have to convert, when the results from any vector operation is always going to be an number[].

[soedirgo]

@steve-chavez does PostgREST convert the JSON array into real[] when inserting into a vector column? I noticed that with plain SQL array[1,2,3] works, but '[1,2,3]'::jsonb doesn't.

[steve-chavez]

@soedirgo No. All it does is pass the json body to json_populate_recordset.

[soedirgo]

OK, so it seems like some numeric types can be converted to vector, which is why the RPC using number[] works. But vector doesn't have a cast to json, so the text representation is used instead [0].

As an alternative to casting, you can add create a computed column like so:

alter table mytable add embedding_arr float4[] generated always as (embedding) stored;

When you query that column it'll be typed as number[].

[0] from the to_json[b] function description