[PostgreSQL] Changing a Column's Data Type

To convert a column to a different data type, use a command like this:

ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);

This will succeed only if each existing entry in the column can be converted to the new type by an implicit cast. If a more complex conversion is needed, you can add a USING clause that specifies how to compute the new values from the old.

PostgreSQL will attempt to convert the column's default value (if any) to the new type, as well as any constraints that involve the column. But these conversions may fail, or may produce surprising results. It's often best to drop any constraints on the column before altering its type, and then add back suitably modified constraints afterwards.


Reference: http://www.postgresql.org/docs/8.1/static/ddl-alter.html


原由：企圖把所有 float4 (real) data type 全部改成 float8 (double precision)。如果 Python 使用 py-postgresql insert 資料，prepared statement

insert into OperatingIncome(
    report_date, 
    stock_code,
    activity_date,
    income
) values($1::text::date, $2, $3::text::date, $4::text::real)

要改成

insert into OperatingIncome(
    report_date, 
    stock_code,
    activity_date,
    income
) values($1::text::date, $2, $3::text::date, $4::text::float8)

留意一下。

Anna Karenina (2012)

非常喜歡
劇本、轉場、音樂、特效相互支援
使得這大部頭鉅作一點都不悶

沒什麼好評論的
有興趣的 看劇前或後 請補課吧！

□ 舊版本
Anna Karenina (1997)

□ 有婦之夫／禁忌之愛／外遇／死亡
《危險關係》
Dangerous Liaisons (1988)
Cruel Intentions (1999) 
The Duchess (2008) 

□ 堅持／等待／求婚橋段／好男無敵
《理性與感情》
《傲慢與偏見》
Sense and Sensibility (1995)
Pride and Prejudice (1995) BBC
Pride & Prejudice (2005)