Re: Extra space when converting number with to_char

Placeholder for a +/- sign so that both positive and negative values have equal lengths.

David J.

On Oct 26, 2012, at 14:50, Samuel Gilbert <samuel.gilbert@ec.gc.ca> wrote:

> Hello,
>
>  I've encountered an unexpected behavior when converting a number to char.  I
> need to match an INTEGER with a VARCHAR(9) that has leading zeros.  When I
> tried to match them using to_char(num, '000000000') I didn't get anything.  I
> later found out that to_char adds a leading space.  This is demonstrated with
> the query below :
>
> SELECT
>   string,
>   length(string),
>   '"' || string || '"' AS showThemToMe
> FROM
>   (SELECT to_char(42, '000000000') AS string) AS example;
>
>   string   | length | showthemtome
> ------------+--------+--------------
>  000000042 |     10 | " 000000042"
>
> Is there a reason why to_char adds a leading space?  Is this a bug?  I can
> easily fix this with trim(leading ' ' from to_char(num, '000000000')), but,
> being of a curious nature, I'd like ton know why I need to do that.
>
> --
> Samuel Gilbert
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general