Tuesday, February 12. 2008How to convert a table column to another data typePrinter FriendlyTrackbacks
Trackback specific URI for this entry
No Trackbacks
Comments
Display comments as
(Linear | Threaded)
I had the same problem. It was caused by string default value. So, try first change the default value e.g. to 0 and then convert:
ALTER TABLE ma_tiger ALTER COLUMN fraddl SET DEFAULT 0; ALTER TABLE ma_tiger ALTER COLUMN fraddl TYPE integer;
this is unnecessarily complicated - the default, when casting a char as a integer should be to just give the converted result, not an error msg or a prompt to write a script.
i want to put my column field id in first column of my table.how can i do this in postgresql...can you suggest any queries for clear this problem.....
Thanks in advance
Niya,
Unfortunately this is one of those things you can't do in PostgreSQL to my knowledge. I think there are talks of changing that. So to do this -- 1) you would have to create a new table with the order you want, copy the data from the old table, 2) drop the old table 3) Then rename the new to old name. In practice there isn't much of a reason to have columns ordered a certain way except if you always like seeing your primary keys listed first in design view of the table. When you do a SELECt SELECT column1, column2, .... the column order in the select will be respected. If you are doing SELECT * which will respect the order of the columns in the table, you should get out of the habit of doing that anyway.
Thanks a lot! Awesome function and I could never have done that on my own. Any idea why a simple cast statement won't do the trick in some cases?
Some things aren't castable or the cast is ambiguous so undefined.
For example if you have the letter 'a' or a space ' ', how would you cast it to a number? What number would it be? That would be a judgement call and would vary depending on what you are doing. Sometimes you'd just want to nullify it and sometimes you really want it to fail because such a thing should never happen and signals questionable data.
To make this applicable for signed values (in the varchar column) you can replace
> WHEN trim($1) SIMILAR TO '[0-9]+' with > WHEN trim($1) ~ '^-?[0-9]+$'
I ran into a very similar problem today, and this gave me the tip I needed to fix it. Thanks!
Why not just use a PostgreSQL built in function? Here's how I solved my similar issue:
> ALTER TABLE people ALTER COLUMN lat TYPE float USING to_number(lat, 'FMS9999.9999999') You can alter the numeric template to fit your needs
You could. For our particular case we didn't because we had a lot of data that had blank (not NULL) and we wanted those to be set to null. The to_number function breaks in that case.
For example: SELECT to_number('', 'FMS9999.9999999'); Gives error: ERROR: invalid input syntax for type numeric: " "
Thank you very much. Its works from me.
Thanks a lot for that script.
I am taking sum of a field having datatype realbut when it gives sum it will round up with th real datatype tange and give the out put like this 1.45678+5
So i m trying to convert it into int,integer or bigint Any body can help me. Thanks Anil mehrotra anil.mehrotra1@gmail.com
I read this tutorial and make this code:
ALTER TABLE "the_table" ALTER COLUMN "the_column" TYPE int4 USING "the_column"::INTEGER; just cast value of the column with direct cast: USING "tmp1"::VARCHAR::TEXT::VARCHAR; |
Entry's LinksQuicksearchCalendar
Categories
Blog Administration |