[fpc-pascal] PostgreSQL: ERROR: operator does not exists: uuid = text

[Michael Van Canneyt]

On Wed, 4 Sep 2013, silvioprog wrote:

> Hello,
> I can't understand how to open a query filtering by a field of type GUID.
> 
> The error message is below (translated from BR to US):
> 
> TPQConnection: Preparation of query failed. (PostgreSQL: ERROR: operator does not exist: uuid = text
> LINE 3: where t1.id = $ 1
>                      ^
> HINT: No operator corresponds with the name(s) and type(s) of argument(s) informed. You need to add explicit type casts.
> 
> I'm sending a small example to reproduce this error in attached. I'm also sending the script that creates the tables.
> 
> Has anyone had this problem?

Your program is wrong.

Postgres parameters are typed. If you do a

   P.asString:='somestring'

then SQL DB will use a parameter of type string to send the data to postgres.
Postgres does not automatically convert between strings to uuid fields (see the error you get).

The solution is to change your SQL to typecast the string parameter to a UUID type:


     q.SQL.Add('where t1.id = (:id)::uuid');

or even

     q.SQL.Add('where t1.id = cast (:id as uuid)');

And all will work well.

Michael.