Postgresql: ERROR: Operator does not exist: bigint ~~* Unknown

Asked

Viewed 2,689 times

0

I’m making a pagination filter in cakephp, but when I type in a phone number to search, it returns the following error:

Error: SQLSTATE[42883]: Undefined Function: 7 ERROR: Operator does not exist: bigint ~~* Unknown LINE 1: ...om"."status_id_destino") WHERE "User"."telefone" ILIKE '%53... ^

HINT: No Operator Matches the Given name and argument type(s). You Might need to add Explicit type Casts.

Any amount I type in this field, I get this error!

(NOTE: Other filters work normally, only this is a field INTEGER that is not working)

Edited: Adding excerpt from the consultation:

 SELECT "User"."id", "User"."telefone", "User"."email" .... etc ...
 FROM "public"."users" AS "User" .... etc ...
 WHERE "User"."telefone" ILIKE '%5332733535%'
        AND "Home"."ativo" = 'TRUE'
        AND "Home"."status_id_destino"
             IN (1, 2, 3, 4, 5, 6, 7, 8, 9)
 ORDER BY "Ose"."id" asc LIMIT 20
postgresql

  • Put the query Techo with the problem, phone is Varying Character?

  • telefone is of the integer type. !

  • 1

    Phone should not an integer, for two reasons, first if you have an 0800 will have problems because the zero left will disappear. An integer represents an amount (something countable) a phone is not countable, it should be a Varying Character, not to apply a LIKE/ILIKE in an integer or numeric type. I suggest you change the column type or create a new one, otherwise try to cast before calling the ilike

  • Hmmm, okay, thanks for the suggestions. ...WHERE "User"."telefone"::varchar ILIKE ...... ??

  • More or less that, I’m not sure if he’ll accept the varchar

  • 1

    Doing the search manually it worked that way, now I’m trying to apply in the code. As soon as I have a return I put here. thanks again!

  • 1

    If you are interested see: CPF or CNPJ field type in VARCHAR or INT database? is practically the same problem (data type).

  • Perfect @rray! !

Show 3 more comments

1 answer

0


Answer: As discussed, it can be done this way:

 SELECT "User"."id", "User"."telefone", "User"."email" .... etc ...
 FROM "public"."users" AS "User" .... etc ...
 WHERE "User"."telefone"::varchar ILIKE '%5332733535%'
        AND "Home"."ativo" = 'TRUE'
        AND "Home"."status_id_destino"
             IN (1, 2, 3, 4, 5, 6, 7, 8, 9)
 ORDER BY "Ose"."id" asc LIMIT 20

Giving a CASTING before the ILIKE or as per the CPF or CNPJ field type in VARCHAR or INT database? provided by rray, the best option would be to change the field type telefone for varchar.

Browser other questions tagged postgresql

You are not signed in. Login or sign up in order to post.