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?