如何在postgres sql中语法指示表以修改其列

Question

我试图通过改变几个列的数据类型从文本到整数/布尔/日期更改表

当我尝试以

方式执行它时

alter table "TimeSeriesData" alter COLUMN "details_id" TYPE BIGINT;

我得到这个错误

ERROR:  column "details_id" cannot be cast automatically to type bigintHINT:  You might need
to specify "USING "details_id"::bigint".uery

当我试图执行

alter table "TimeSeriesData" alter COLUMN "details_id" TYPE BIGINT USING (details_id::BIGINT);

我得到了以下错误

ERROR:  column "details_id" does not existLINE 1: ... COLUMN "details_id" TYPE BIGINT USING 
(timeSeries...^HINT:  Perhaps you meant to reference the column "TimeSeriesData.details_id".uery

最后尝试了这种方法，根据SQL GUI

的建议

alter table "TimeSeriesData" alter COLUMN "TimeSeriesData"."details_id" TYPE BIGINT 
USING (details_id::BIGINT);

再次，它让这个错误

ERROR:  syntax error at or near "."LINE 1: ...eriesData" alter COLUMN 
"TimeSeriesData"."details_...  ^uery 

我使用TimescaleDB和heidiSQL作为它的GUI。你能告诉我如何继续下去吗

Answer 1

您可以遵循以下步骤:

• 新建列
• 迁移(强制转换)数据到此列。
• 检查数据迁移。
• 删除旧列
• 重命名新列。
例如:

CREATE TABLE test (
id Int PRIMARY KEY,
my_bool VARCHAR(5)
);
INSERT INTO test (id, my_bool)
VALUES (1, '1');
INSERT INTO test (id, my_bool)
VALUES (2, '0');
ALTER TABLE test ADD COLUMN my_bool_2 int;
UPDATE test SET my_bool_2 = CAST(my_bool as int);
ALTER TABLE test DROP COLUMN my_bool;
ALTER TABLE test RENAME COLUMN my_bool_2 TO my_bool;

Answer 2

我在这里试了一个小例子，我想你错过了USING条款。

ALTER TABLE my_table ALTER COLUMN value TYPE BIGINT USING value::BIGINT;

下面是完整的例子:

DROP TABLE my_table;
CREATE TABLE my_table ( time TIMESTAMP NOT NULL,  value varchar);
SELECT create_hypertable('my_table', 'time');

INSERT INTO my_table (time, value) VALUES
('2021-08-26 10:09:00.01', '1012311'),
('2021-08-26 10:09:00.08',  '1022220'),
('2021-08-26 10:09:00.40',  '103333000');
ALTER TABLE my_table ALTER COLUMN value TYPE BIGINT USING value::BIGINT;

然后，检查结果:

tsdb=> table my_table;
┌────────────────────────┬───────────┐
│          time          │   value   │
├────────────────────────┼───────────┤
│ 2021-08-26 10:09:00.01 │   1012311 │
│ 2021-08-26 10:09:00.08 │   1022220 │
│ 2021-08-26 10:09:00.4  │ 103333000 │
└────────────────────────┴───────────┘
(3 rows)