将 varchar 类型的 postgres 字段转换为数字

发布于 2024-10-30 21:30:58 字数 286 浏览 0 评论 0原文

我有查询修改 postgres 中超过 200 万条记录。
问题是其中一个字段的类型为 VARCHAR，我需要对其进行一些数学运算。现在我正在将字段转换为数字，如下所示：-

CAST(attribute as numeric)

整个查询大约需要 4 小时才能运行。
我正在寻找缩短执行时间的方法。在执行查询之前，有什么方法可以先将字段类型从 varchar 更改为 numeric 吗？我无法使用 alter table alter columns type 来执行此操作。

原文

I have query to modify 2 million over records in postgres.
The problem is that one of the field is of type VARCHAR, and I need to do some mathematical operations on it. Right now I'm casting the field to numeric like this :-

CAST(attribute as numeric)

The whole query takes approximately 4 hours to run.
I am looking at ways to fasten then execution time. Are the any way I can change the field type from varchar to numeric first before I execute the query? I can't use alter table alter column type to do this.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

聆听风音 2024-11-06 21:30:58

如果您无法长时间阻止表读取，毫无疑问，最好的方法是创建“第二个”表并进行后续更新，就像@OMG Ponies 所说的那样。

另外，如果触发器中的任何一个将使用新值执行某些操作（例如：日志的触发器，因为我们不会更改它本身的“值”），请尝试禁用触发器。这可以大大提高性能，具体取决于您的触发器的作用。

像这样的东西：

-- STEP 1: CREATING SECOND TABLE
START TRANSACTION;
CREATE TABLE MY_SECOND_TABLE AS SELECT <YOURKEYFIELDNAME>, (ATTRIBUTE AS NUMERIC) AS ATTRIBUTE FROM MY_TABLE;    
CREATE UNIQUE INDEX UI_MY_SECOND_TABLE ON MY_SECOND_TABLE (<YOURKEYFIELDNAME>);
COMMIT;

-- STEP 2: UPDATING A SOURCE TABLE
START TRANSACTION;
ALTER TABLE MY_TABLE DISABLE TRIGGER ALL;
ALTER TABLE MY_TABLE DROP COLUMN ATTRIBUTE;
ALTER TABLE ATTRIBUTE ADD ATTRIBUTE INTEGER;
UPDATE MY_TABLE T SET ATTRIBUTE = (SELECT ATTRIBUTE FROM MY_SECOND_TABLE T2 WHERE T2.<YOURKEYFIELDNAME> = T.<YOURKEYFIELDNAME>);    
ALTER TABLE MY_TABLE ENABLE TRIGGER ALL;
COMMIT;

-- DROP SECOND TABLE
DROP TABLE MY_SECOND_TABLE;

If you can't block the table for reading for such long time, there's not doubt that the best approuch is create a "second" table and do a subsequent update, like @OMG Ponies said.

Also, try to disable triggers if any of them will do something with new value (eg: log's trigger, since we are not changing the "value" it self). This can increase performance a lot, depending what your triggers do.

Something like this:

-- STEP 1: CREATING SECOND TABLE
START TRANSACTION;
CREATE TABLE MY_SECOND_TABLE AS SELECT <YOURKEYFIELDNAME>, (ATTRIBUTE AS NUMERIC) AS ATTRIBUTE FROM MY_TABLE;    
CREATE UNIQUE INDEX UI_MY_SECOND_TABLE ON MY_SECOND_TABLE (<YOURKEYFIELDNAME>);
COMMIT;

-- STEP 2: UPDATING A SOURCE TABLE
START TRANSACTION;
ALTER TABLE MY_TABLE DISABLE TRIGGER ALL;
ALTER TABLE MY_TABLE DROP COLUMN ATTRIBUTE;
ALTER TABLE ATTRIBUTE ADD ATTRIBUTE INTEGER;
UPDATE MY_TABLE T SET ATTRIBUTE = (SELECT ATTRIBUTE FROM MY_SECOND_TABLE T2 WHERE T2.<YOURKEYFIELDNAME> = T.<YOURKEYFIELDNAME>);    
ALTER TABLE MY_TABLE ENABLE TRIGGER ALL;
COMMIT;

-- DROP SECOND TABLE
DROP TABLE MY_SECOND_TABLE;

回复收藏 0 原文

~没有更多了~