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.

Improve this question
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.

Improve this answer
  • 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? – Joishi Bodio Jan 29 '14 at 15:05
  • @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. – Craig Ringer Jan 29 '14 at 23:13
  • I am subscribed to many of the mailing lists. But ok. I will see about giving that a go a 2nd time. – Joishi Bodio Jan 29 '14 at 23:48
  • 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) – ypercubeᵀᴹ Feb 28 '14 at 10:19
  • @ypercube Yes, that's certainly the appropriate thing to do. – Craig Ringer Feb 28 '14 at 12:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.