Extra space when converting number with to_char

From: Samuel Gilbert <samuel(dot)gilbert(at)ec(dot)gc(dot)ca>
To: pgsql-general(at)postgresql(dot)org
Subject: Extra space when converting number with to_char
Date: 2012-10-26 18:50:15
Message-ID: 201210261450.15855.samuel.gilbert@ec.gc.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Johnston 2012-10-26 19:23:50 Re: Extra space when converting number with to_char
Previous Message D T 2012-10-26 17:43:28 Function Profiler in Postgre