一月 22, 2025
摘要：本教程逐步向您展示如何使用ALTER TABLE语句更改列的数据类型。
目录
PostgreSQL 更改列类型简介
在关系数据库中，通常，您应该使用最适合您的数据表示形式的数据类型。但是，一些应用程序中，开发者会错误地将日期或数字存储为文本，或者将日期存储为整数。这种现象并不少见，要修复它可能是一个相当大的挑战，因为当您想更改用于特定列的数据类型时，您需要从一种数据类型转换为另一种数据类型。根据数据的当前格式，它可能很容易修复，也可能修复起来会很复杂。
PostgreSQL 提供了更改列类型的语句，来做到这一点。
PostgreSQL 更改列类型语句
要更改列的数据类型，请使用 ALTER TABLE 语句，如下：
ALTER TABLE table_name
ALTER COLUMN column_name [SET DATA] TYPE new_data_type;
让我们更详细地检查该语句：
- 首先，在
ALTER TABLE关键字后指定要更改的列的表的名称。 - 其次，在
ALTER COLUMN子句后指定要更改数据类型的列的名称。 - 第三，在
TYPE关键字之后为列提供新的数据类型。SET DATA TYPE和TYPE是等价的。
要在单个语句中更改多个列的数据类型，可以使用多个ALTER COLUMN子句，如下所示：
ALTER TABLE table_name
ALTER COLUMN column_name1 [SET DATA] TYPE new_data_type,
ALTER COLUMN column_name2 [SET DATA] TYPE new_data_type,
...;
在此语法中，您在每个ALTER COLUMN子句后添加一个逗号 (,) 。
PostgreSQL 允许您通过添加USING子句将列的值转换为新值，同时更改其数据类型，如下所示：
ALTER TABLE table_name
ALTER COLUMN column_name TYPE new_data_type USING expression;
USING子句指定一个表达式，允许您将旧值转换为新值。
如果省略USING子句，PostgreSQL 将隐式将值转换为新值。如果转换失败，PostgreSQL 将发出错误，并建议您提供带表达式的USING子句用于数据转换。
USING关键字后面的表达式可以像column_name::new_data_type一样简单，如price::numeric，也可以像自定义函数一样复杂。
PostgreSQL 更改列类型示例
让我们创建一个新表，名为assets，并向该表中插入一些行以进行演示。
CREATE TABLE assets (
id serial PRIMARY KEY,
name TEXT NOT NULL,
asset_no VARCHAR NOT NULL,
description TEXT,
location TEXT,
acquired_date DATE NOT NULL
);
INSERT INTO assets(name,asset_no,location,acquired_date)
VALUES('Server','10001','Server room','2017-01-01'),
('UPS','10002','Server room','2017-01-01');
要将name列的数据类型更改为 VARCHAR，请使用以下语句：
ALTER TABLE assets
ALTER COLUMN name TYPE VARCHAR;
以下语句将description和location列的数据类型从 TEXT 更改为VARCHAR：
ALTER TABLE assets
ALTER COLUMN location TYPE VARCHAR,
ALTER COLUMN description TYPE VARCHAR;
要将asset_no列的数据类型更改为整型，请使用以下语句：
ALTER TABLE assets
ALTER COLUMN asset_no TYPE INT;
PostgreSQL 发出了一个错误和一个非常有用的提示：
ERROR: column "asset_no" cannot be cast automatically to type integer
HINT: You might need to specify "USING asset_no::integer".
以下语句将USING子句添加到上述语句中：
ALTER TABLE assets
ALTER COLUMN asset_no TYPE INT
USING asset_no::integer;
它按预期工作了。
高级示例
让我们创建一个简单的表，名为 t1，并向表中插入一些行以进行演示。
CREATE TABLE t1 ( a int, b text );
INSERT INTO t1(a, b)
VALUES(1, '0101-2019'),
(2, '0102-2019'),
(3, '0103-2019');
SELECT * FROM t1;
a | b
---+-----------
1 | 0101-2019
2 | 0102-2019
3 | 0103-2019
(3 rows)
ALTER TABLE t1 ALTER COLUMN b TYPE date USING (b::date);
ERROR: invalid input syntax for type date: "0101-2019"
由于 PostgreSQL 不知道如何进行转换，因此会失败。但是，您还可以通过提供一个函数来做到这一点，可以让函数完全按照您想要的方式进行转换：
CREATE OR REPLACE FUNCTION f_convert_to_date(pv_text IN text)
RETURNS date
AS $$
DECLARE
BEGIN
RETURN date(substr(pv_text, 6, 4) || substr(pv_text, 1, 4));
END;
$$ LANGUAGE plpgsql;
当然，您需要添加正确的逻辑来解析输入的字符串，以让函数返回匹配的日期，比如像上面的函数一样。接下来，我们可以使用这个函数来更改列类型：
ALTER TABLE t1 ALTER COLUMN b TYPE date USING (f_convert_to_date(b));
该列从 text 转换成了 date。我们通过调用包含了转换逻辑的函数，提供了执行该操作的确切方法。只要函数的输出符合所需的数据类型，并且您没有犯任何错误，您就有可能从任何源数据类型转换到任何目标数据类型。
总结
在本教程中，您学习了如何使用ALTER TABLE ALTER COLUMN语句来更改列的类型。