PostgreSQL =>没有时区的ALTER TABLE->带有时区，对TZ使用select [英] PostgreSQL => ALTER TABLE without timezone -> with timezone, using select for TZ

问题描述

我正在尝试转换列，并保留指定时区中的数据.我有一个脚本，但最后在 SELECT 上出错.

I'm trying to convert the column, and preserve the data in the specified timezone. I have a script, but it errors out on the SELECT at the end.

ALTER TABLE schema.table 
ALTER COLUMN column_date TYPE TIMESTAMP WITH TIME ZONE
USING column_date AT TIME ZONE (SELECT value FROM schema.table WHERE id = 'timezone');

ERROR:  cannot use subquery in transform expression

我存储了时区，但是尝试将其应用于脚本会带来挑战.我知道我可以将时区硬编码为"EST | CST | PST"，等等.但是我有多个数据库需要应用(每个数据库有多个变更)，因此需要在 SELECT 结束.有没有办法做到这一点?

I have time zones stored, but trying to pull that back to apply to the script is posing a challenge. I know I can simply hardcode the timezone as 'EST|CST|PST', etc. but I have multiple databases this needs to be applied to (with multiple alters per DB), hence the required SELECT at the end. Is there a way to accomplish this?

关于这个问题，我提出了几个问题；虽然有些接近，但他们并不能完全满足需求(我很抱歉，如果重复的话，我已经花了一段时间寻找答案).

I've poured over a few questions on this subject; while some came close, they don't quite meet the needs (My apologies if this is a duplicate, I've spent a while searching for an answer).

[已解决]:使用2个单独的查询

ALTER TABLE schema.table 
ALTER COLUMN column_date TYPE TIMESTAMP WITH TIME ZONE;

UPDATE schema.table 
SET column_date = column_date AT TIME ZONE 
    (SELECT value FROM schema.table WHERE id = 'timezone');

推荐答案

您可以使用动态SQL:

You can use dynamic SQL:

DO
$$BEGIN
   EXECUTE format('ALTER TABLE ... AT TIME ZONE %L',
                  (SELECT value FROM atable ...));
END;$$;

这篇关于PostgreSQL =>没有时区的ALTER TABLE->带有时区，对TZ使用select的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！