postgresql Postgres Alter table 将列类型从 char 转换为 bigint
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13809173/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres Alter table to convert column type from char to bigint
提问by Lighthart
Possible Duplicate:
how to change column datatype from character to numeric in postgresql 8.4
If I have a field of type varchar (and all the values are null or string representations of numbers) how do I use alter table to convert this column type to bigint?
如果我有一个 varchar 类型的字段（并且所有值都是数字的 null 或字符串表示形式），我如何使用 alter table 将此列类型转换为 bigint？
回答by araqnid
To convert simply by parsing the string (casting):
简单地通过解析字符串（转换）来转换：
alter table the_table alter column the_column type bigint using the_column::bigint
In fact, you can use any expression in terms of the_columninstead of the_column::bigintto customise the conversion.
事实上，你可以使用任何表达方面the_column，而不是the_column::bigint自定义转换。
Note this will rewrite the table, locking out even readers until it's done.
请注意，这将重写表，甚至在完成之前锁定读者。
回答by Scott S
You could create a temporary column of type bigint, and then execute SQL like
您可以创建一个类型为的临时列bigint，然后像这样执行 SQL
UPDATE my_table SET bigint_column=varchar_column::bigint;
Then drop your varchar_column and rename bigint_column. This is kinda roundabout, but will not require a custom cast in postgres.
然后删除您的 varchar_column 并重命名 bigint_column。这有点迂回，但不需要在 postgres 中进行自定义转换。
回答by Eric Leschinski
How to convert a string column type to numeric or bigint in postgresql
如何在 postgresql 中将字符串列类型转换为 numeric 或 bigint
Design your own custom cast from string to bigint. Something like this:
设计您自己的从字符串到 bigint 的自定义转换。像这样的东西：
CREATE OR REPLACE FUNCTION convert_to_bigint(v_input text)
RETURNS BIGINT AS $$
DECLARE v_bigint_value BIGINT DEFAULT NULL;
BEGIN
BEGIN
v_bigint_value := v_input::BIGINT;
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE 'Invalid bigint value: "%". Returning something else.', v_input;
RETURN 0;
END;
RETURN v_bigint_value;
END;
Then create a new table fixed_table_with_bigintwith the same parameters as the old table except change the string column into the bigint column.
然后使用fixed_table_with_bigint与旧表相同的参数创建一个新表，只是将字符串列更改为 bigint 列。
Then insert all the rows from the previous table (using the custom cast convert_to_integer) into the new table:
然后将上一个表中的所有行（使用自定义强制转换convert_to_integer）插入到新表中：
insert into fixed_table_with_bigint
select mycolumn1,
convert_to_bigint(your_string_bigint_column),
mycolumn3
from incorrect_table
You may have to modify convert_to_bigintin order to handle strings which are not numbers, blankstrings, nulls, control characters and other Weirdness.
您可能需要修改convert_to_bigint以处理不是数字、空白字符串、空值、控制字符和其他奇怪的字符串。
Then delete the first table and rename the 2nd table as the first table.
然后删除第一个表并将第二个表重命名为第一个表。
