pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUnable To Alter Data Type
4+ messages / 2 participants
[nested] [flat]
* Unable To Alter Data Type
@ 2012-01-11 23:52 Carlos Mennens <carlos.mennens@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Carlos Mennens @ 2012-01-11 23:52 UTC (permalink / raw)
To: pgsql-sql
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 null
cust_name | character(50) | not null
cust_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
------------
44444
43333
42222
88888
54545
32765
(6 rows)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Unable To Alter Data Type
@ 2012-01-12 00:13 David Johnston <polobo@yahoo.com>
parent: Carlos Mennens <carlos.mennens@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: David Johnston @ 2012-01-12 00:13 UTC (permalink / raw)
To: 'Carlos Mennens' <carlos.mennens@gmail.com>; pgsql-sql
-----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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Unable To Alter Data Type
@ 2012-01-12 00:30 Carlos Mennens <carlos.mennens@gmail.com>
parent: David Johnston <polobo@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: Carlos Mennens @ 2012-01-12 00:30 UTC (permalink / raw)
To: David Johnston <polobo@yahoo.com>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Unable To Alter Data Type
@ 2012-01-12 01:21 David Johnston <polobo@yahoo.com>
parent: Carlos Mennens <carlos.mennens@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: David Johnston @ 2012-01-12 01:21 UTC (permalink / raw)
To: Carlos Mennens <carlos.mennens@gmail.com>; +Cc: pgsql-sql
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; even if the only possibly valid values are numbers.
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2012-01-12 01:21 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-01-11 23:52 Unable To Alter Data Type Carlos Mennens <carlos.mennens@gmail.com>
2012-01-12 00:13 ` David Johnston <polobo@yahoo.com>
2012-01-12 00:30 ` Carlos Mennens <carlos.mennens@gmail.com>
2012-01-12 01:21 ` David Johnston <polobo@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox