CAST(integer_field AS character) truncates trailing zeros

On Thu, Jul 31, 2008 at 4:03 PM, Warren Bell <warren@clarksnutrition.com> wrote:

I am trying to cast an int to a character. The int is the number 1000 it
gets cast down to "1" and not "1000". How do I cast from int to character
without loosing the trailing zeros?

Please supply the exact syntax that you're using to do the cast.

-Doug

On Thu, Jul 31, 2008 at 1:03 PM, Warren Bell <warren@clarksnutrition.com> wrote:

I am trying to cast an int to a character. The int is the number 1000 it
gets cast down to "1" and not "1000". How do I cast from int to character
without loosing the trailing zeros?

Here is what I get when I try:

postgres=# select cast( cast( 1000 as integer ) as char );
bpchar
--------
1
(1 row)

postgres=# select cast( cast( 1000 as integer ) as char(10) );
bpchar
------------
1000
(1 row)

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

--- On Thu, 7/31/08, Warren Bell <warren@clarksnutrition.com> wrote:

From: Warren Bell <warren@clarksnutrition.com>
Subject: [GENERAL] CAST(integer_field AS character) truncates trailing zeros
To: pgsql-general@postgresql.org
Date: Thursday, July 31, 2008, 8:03 PM
I am trying to cast an int to a character. The int is the
number 1000 it
gets cast down to "1" and not "1000".
How do I cast from int to
character without loosing the trailing zeros?

--
Thanks,

Warren Bell

work to me

template1=# select cast('1000' as varchar);
varchar
---------
1000
(1 fila)

template1=#

On Thu, Jul 31, 2008 at 4:17 PM, Richard Broersma
<richard.broersma@gmail.com> wrote:

On Thu, Jul 31, 2008 at 1:03 PM, Warren Bell <warren@clarksnutrition.com> wrote:

I am trying to cast an int to a character. The int is the number 1000 it
gets cast down to "1" and not "1000". How do I cast from int to character
without loosing the trailing zeros?

Here is what I get when I try:

postgres=# select cast( cast( 1000 as integer ) as char );
bpchar
--------
1
(1 row)

AFAIK, CHAR means CHAR(1), so there are not enough characters to
contain the result and it gets truncated. Maybe TEXT should be used
instead, or VARCHAR?

-Doug

I guess it would help if I cast to the correct type. I was doing cast(
cast( 1000 as integer ) as char ) instead of character varying, char(n)
or text.

Thanks,

Warren

Warren Bell wrote:

I am trying to cast an int to a character. The int is the number 1000
it gets cast down to "1" and not "1000". How do I cast from int to
character without loosing the trailing zeros?

--
Thanks,

Warren Bell
909-645-8864
warren@clarksnutrition.com