varchar comparison and trim()
От | ta@lavabit.com |
---|---|
Тема | varchar comparison and trim() |
Дата | |
Msg-id | 53370.87.252.128.85.1355940794.squirrel@lavabit.com обсуждение исходный текст |
Список | pgsql-novice |
What would be the easiest way to make PG perform string comparison consistently across all string types? By 'consistent' I refer to this trailing space handling: (possibly it's standard but I find it somewhat inconvenient) select 'aa'::char(4) = 'aa '::char(3) => T select 'aa'::varchar(4) = 'aa '::varchar(3) => F select 'aa'::char(4) = 'aa '::varchar(3) => T etc I've tried to redefine varchar to varchar operators applying Trim() to the arguments (have put them in separate "myschema" to avoid possible interference with who knows what) and that seems to work fine (not quite sure about performance loss and those optimizer hints). But during my tests sometimes, somehow, varchar columns having "unique" constraint defined, manage to accept both 'aa' and 'aa ' values. Either I missed to set search_path correctly every time or there is some procedure (running possibly under 'public' search_path) that performs unique check using standard operators... So, this solution seems a bit "picky". Might I have to convert all varchar columns to char if they are covered by "unique" constraint? This way or another, having some sort of permanent "ANSI_PADDING" setting would be nice. By the way, I managed to redefine various combinations of text to char, text to varchar etc, but not "text to text" operators. PG9.2 quietly ignores that redefinition. So, I finally gave up using "text" at all and converted all text columns that are likely to be used in comparison to varchar (unbounded). Have I overlooked something? Any suggestions?
В списке pgsql-novice по дате отправления: