[pgbr-geral] Desconsiderar maiúsculo/minúsculo em campos varchar

[izaque Maciel]

Boa tarde, alguns dias atrás, precisei que os campos varchar funcionassem
como Case Insensitive, mas tudo que o pessoal me retornou foi algo como:

select upper(c.nome_cli) from clientes c like upper(c.nome_cli), e um outro
usuário me reportou que eu poderia utilizar o tipo de dado CITEXT, que no
delphi não
ficou legal. Foi então que por um acaso procurando uma outra coisa
encontrei este artigo abaixo, caso alguém queira utilizar:

http://www.postgresonline.com/journal/archives/24-Using-MS-Access-with-PostgreSQL.html


   PostgreSQL is case-sensitive

   One of the most annoying things for people coming from a Windows
   environment is that PostgreSQL is case-sensitive whereas MS Access
   in-general is not (except when querying case sensitive databases).
   Explaining this to users and training them on case sensitivity is just a
   lot of hassle, not to mention the time-loss of having to upper case things.
   Hopefully this will change in the future so that PostgreSQL supports
   different collation depending field by field similar to the way SQL Server
   2005 does. Needless to say, when running a query in MS Access, one has
   three options:
   1. Write your query along the lines of *upper(somefield) LIKE
  UCase('abc%')* and make sure you have a functional index on
  upper(somefield)
  2. Use the custom data type such as *citext* which you need to
  compile yourself.
   - or Put functional upper(somefield) indexes on your common fields and
  use the freedom that PostgreSQL gives you to redefine varchar
operators in
  your database by doing the below. NOTE that this gives you the benefit of
  not having to redefine varchar fields as citext or anything like
that thus
  making it more portable to transfer back and forth between non-case
  sensitive dbs or use the same schema as non-case sensitive dbs. Note we
  couldn't do the below with *text* because that is defined high up and
  can not be overwritten. We can overwrite the behavior of varchars however
  because varchars get implicitly cast to text and use the text
operators. By
  using PostgreSQL's operator overload feature, we can define special
  behavior for varchar when used in comparators. When Postgres
sees there is
  such an operator, it will use that instead of cast varchar to text and
  using the default text operators. The downside is that this will not work
  with PostgreSQL text (NOTE: varchar in PostgreSQL/ANSI SQL maps
to text in
  MS Access and text in PostgreSQL/ANSI maps to memo in MS Access
- all very
  confusing) . In most cases this is a non-issue since most
searches are done
  on short Access text fields rather than memo fields. *NOTE: Use with
  caution. We haven't thoroughly tested this technique to catch all the
  possible situations where it can go wrong. It seems to behave correctly
  from our naive tests.*
  - Doing the above allows us to define a query like this in MS Access
  - [image: Case insensitive search] Which yields: [image: Johns and
  Farns]
   -


   CREATE OR REPLACE FUNCTION ci_caseinsmatch(varchar, varchar) RETURNS boolean
   AS $$
  SELECT UPPER($1)::text = UPPER($2)::text;
   $$
   LANGUAGE sql
   IMMUTABLE STRICT;CREATE OPERATOR = (
   PROCEDURE = ci_caseinsmatch,
   LEFTARG = varchar,
   RIGHTARG = varchar,
   COMMUTATOR = =,
   NEGATOR = <>
   );CREATE FUNCTION ci_like(varchar, varchar) RETURNS boolean
   AS $$
  SELECT UPPER($1)::text LIKE UPPER($2)::text;
   $$
   LANGUAGE sql;CREATE OPERATOR ~~(
 PROCEDURE = ci_like,
 LEFTARG = varchar,
 RIGHTARG = varchar,
 RESTRICT = likesel,
 JOIN = likejoinsel);   

   And can now be written in SQL even in PgAdmin without all that messy
   upper lower stuff and still uses indexes if you have them defined on say
   upper(first_name) or doing range case-insensitive searches e.g.
   (customer.last_name between 'f' and 'h') :
SELECT customer.*
   FROM customer
   WHERE customer.last_name
Like 'Farns%' OR customer.first_name = 'Jim';
Which will give you all customers with first name Jim or last name like
   Farns. Best of all, if you put in a functional index on last name and first
   name like below, it will use those indexes when doing equality or between
   ranges etc..

   Eu estou utilizando e até o momento está funcionando como eu queria.
   Acho que combinando o "Unaccent" ele também irá desconsiderar os acentos,
   mas isso não testei ainda.
___
pgbr-geral mailing list
pgbr-geral@listas.postgresql.org.br
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral

Re: [pgbr-geral] Desconsiderar maiúsculo/minúsculo em campos varchar

[Leonardo Cezar]

On Mon, Apr 1, 2013 at 1:45 PM, izaque Maciel wrote:

> Boa tarde, alguns dias atrás, precisei que os campos varchar funcionassem
> como Case Insensitive, mas tudo que o pessoal me retornou foi algo como:
>
> select upper(c.nome_cli) from clientes c like upper(c.nome_cli), e um
> outro usuário me reportou que eu poderia utilizar o tipo de dado CITEXT,
> que no delphi não
> ficou legal. Foi então que por um acaso procurando uma outra coisa
> encontrei este artigo abaixo, caso alguém queira utilizar:
>



O problema de funções e operadores criadas no catálogo são as atualizações
e migrações que além de dramáticas por vezes causam comportamentos
estranhos no otiimizador.


O operador (ILIKE - repare o "I" no início) parece tão mais simples:

SELECT 'AbC' ILIKE 'abc';

Embora esteja em não-conformidade com o padrão SQL, costuma ser útil,
simples e funcional com a maioria dos ORMs distribuídos para PostgreSQL
seja a linguagem que for.

Abraço!

-Leo
-- 
Leonardo Cezar
http://www.postgreslogia .com
___
pgbr-geral mailing list
pgbr-geral@listas.postgresql.org.br
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral