Thread: Unable To Alter Data Type

I have an issue I can't figure out. I have the following TABLE:


tysql=# \d customers        Table "public.customers"   Column    |      Type      | Modifiers
--------------+----------------+-----------cust_id      | character(10)  | not nullcust_name    | character(50)  | not
nullcust_address| character(50)  |cust_city    | character(50)  |cust_state   | character(5)   |cust_zip     |
character(10) |cust_country | character(50)  |cust_contact | character(50)  |cust_email   | character(255) |
 
Indexes:   "customers_pkey" PRIMARY KEY, btree (cust_id)
Referenced by:   TABLE "orders" CONSTRAINT "fk_orders_customers" FOREIGN KEY
(cust_id) REFERENCES customers(cust_id)

Now I'm attempting to ALTER the field 'cust_zip' TYPE from character
to integer however I'm getting the following error:

tysql=# ALTER TABLE customers ALTER COLUMN cust_zip TYPE bigint;
ERROR:  column "cust_zip" cannot be cast to type bigint

I thought perhaps the stored data in the field conflicted with the
data type but I can't see why:

tysql=# SELECT cust_zip FROM customers; cust_zip
------------444444333342222888885454532765
(6 rows)

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Carlos Mennens
Sent: Wednesday, January 11, 2012 6:53 PM
To: PostgreSQL (SQL)
Subject: [SQL] Unable To Alter Data Type


Now I'm attempting to ALTER the field 'cust_zip' TYPE from character to
integer however I'm getting the following error:

----------------------------------------------------------------------------
--------

I think there is a rule in PostgreSQL that says that any field with "zip" in
the name MUST BE of a character type :)

You seriously do not want to turn this into an integer (big or otherwise) -
ZIP (POSTAL) CODES ARE NOT NUMERIC; THEY ARE TEXT!

If someone is telling you to make this change then tell them they are wrong
and whatever requirement are in place on the other end need to be modified -
not the table you indicate.

I do confirm, however, that a direct cast from character to bigint works and
thus you SHOULD be able to perform the TYPE alteration without adding the
USING (expression) clause.  So either this is a bug OR, more likely,
character-to-bigint casting is not implicit (or an assignment cast, whatever
that is) and so you must resort to the USING clause - which does work.

[ ... ALTER COLUMN zip_code TYPE bigint USING (zip_code::bigint) ]

http://www.postgresql.org/docs/9.0/interactive/sql-altertable.html

This is on 9.0 for me.

However, I will say again, you DO NOT WANT TO ACTUALLY DO THIS!

The specific issue is that some US Postal Code begin with a zero ( 0 ) and
so whenever you want to the zip_code value you need to pad leading zeros if
the length is less than 5.  Now consider that a full zip_code can be in 5+4
format with an embedded hyphen and you no longer can even store it as
numeric.  If you deal with Canada (and maybe Mexico) at all then spaces and
letters become acceptable characters within the zip_code.

David J.

On Wed, Jan 11, 2012 at 7:13 PM, David Johnston <polobo@yahoo.com> wrote:
> However, I will say again, you DO NOT WANT TO ACTUALLY DO THIS!
>
> The specific issue is that some US Postal Code begin with a zero ( 0 ) and
> so whenever you want to the zip_code value you need to pad leading zeros if
> the length is less than 5.  Now consider that a full zip_code can be in 5+4
> format with an embedded hyphen and you no longer can even store it as
> numeric.  If you deal with Canada (and maybe Mexico) at all then spaces and
> letters become acceptable characters within the zip_code.

David - Thank you for that great info / explanation. Very informative
and helpful. I was not required to make this change but rather just
goofing off attempting to learn SQL as I'm rather terrible at it. Can
you tell me if there's an organized cheat sheet or something
documented in regards to data types commonly used for commonly used
field association? I think that's great for people who can't look at
the documentation and clearly understand specific definitions or
assumed categorization based on the type definition.

On Jan 11, 2012, at 19:30, Carlos Mennens <carlos.mennens@gmail.com> wrote:

> On Wed, Jan 11, 2012 at 7:13 PM, David Johnston <polobo@yahoo.com> wrote:
>> However, I will say again, you DO NOT WANT TO ACTUALLY DO THIS!
>>
>> The specific issue is that some US Postal Code begin with a zero ( 0 ) and
>> so whenever you want to the zip_code value you need to pad leading zeros if
>> the length is less than 5.  Now consider that a full zip_code can be in 5+4
>> format with an embedded hyphen and you no longer can even store it as
>> numeric.  If you deal with Canada (and maybe Mexico) at all then spaces and
>> letters become acceptable characters within the zip_code.
>
> David - Thank you for that great info / explanation. Very informative
> and helpful. I was not required to make this change but rather just
> goofing off attempting to learn SQL as I'm rather terrible at it. Can
> you tell me if there's an organized cheat sheet or something
> documented in regards to data types commonly used for commonly used
> field association? I think that's great for people who can't look at
> the documentation and clearly understand specific definitions or
> assumed categorization based on the type definition.

If you can perform reasonable arithmetic on the field value you encode it as a number otherwise  you should use text;
evenif the only possibly valid values are numbers. 

David J.