RIGHT() function in postgresql 9.1 not behaving as anticipated?

Question

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.

Answer 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.

• Joishi Bodio: Thank you for replying - we're using char in this instance because we're consuming a fixed width data file provided to us by a 3rd party. I tried posting to one of the postgres mailing lists but it never appeared in the stream (even though I didn't get a bounce) - perhaps I did something wrong when I sent the email? Could you let me know the proper way of posting to the mailing list?
• Craig Ringer: @JoishiBodio Unless you subscribe, pgsql-general is moderated. So you have to wait for someone to read and approve the email. If you're subscribed and a post fails to appear, it might be filtered due to very large attachments or attachments of disallowed types (e.g. .zip). Better to ask the list admin rather than me, though.
• Joishi Bodio: I am subscribed to many of the mailing lists. But ok. I will see about giving that a go a 2nd time.
• ypercubeᵀᴹ: You could use VARCHAR(2) and add a constraint for the length=2. See this answer by @Erwin who suggests TEXT and CHECK(length(col) = 2)
• Craig Ringer: @ypercube Yes, that's certainly the appropriate thing to do.