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 [email protected] https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
