1

The following is with regard to PostgresQL 9.1 :

I am using the right() and left() string functions to fix a few instances where data was input into my database being “off by one” .. so I’m doing something along the lines of right(field1,1)||left(field2,-1) to get correct values and such. In the process, I encountered an unexpected result on a char(2) column.

If I do SELECT RIGHT('A ',1) it gives me the expected ' ' returned.

However, if I do SELECT RIGHT('A '::CHAR(2),1) it gives me 'A'.

Why is it doing this? Is it a bug? I tried SELECT RIGHT('A '::CHAR(3),1) and it also gives 'A' as a result.

Joishi Bodio
  • 3,508
  • 2
  • 17
  • 20

1 Answers1

1

The CHAR data type is awful. Avoid it if at all possible, its padding rules are insane. PostgreSQL wouldn't support it at all if the standard didn't require support for it.

It's not clear whether this is intended behaviour or a bug. Usually, leading white-space gets stripped from CHAR before processing, and I suspect that for RIGHT, Pg is stripping the whitespace from the right, as if this were RTL text.

I suggest raising this for discussion on pgsql-general. If nothing else, it clearly warrants a documentation note.

Craig Ringer
  • 57,821
  • 6
  • 162
  • 193