提问人：Vasilii Rogin 提问时间：9/29/2022 更新时间：11/7/2022 访问量：38
如何移动postgresql数据库上的所有时间戳日期？
How to move all timestamptz dates on the postgresql database?
问：
我有一些种子数据库的postgresql转储。这个转储是几个月前创建的，所以所有的数据都是关于过去的。使用过去的数据进行开发不是很方便，因为我必须始终在 UI 中滚动到过去的日期。
我正在考虑按特定偏移量自动移动数据库中的每个字段。通过一些脚本听起来可行，该脚本将抛出数据库模式，查找每个时间戳字段，然后为每个字段构建 SQL 更新。timestamptz
那么，有没有现成的解决方案呢？
答：
0赞
Dean Van Greunen
9/29/2022
#1
只需将内容添加到数据库中，然后使用此查询更新它，更改列名、表名和您希望它递增的天数
UPDATE table_name
SET timestamptz = timestamptz + interval '1' day
WHERE 1 = 1;
评论
0赞
Vasilii Rogin
9/29/2022
我在许多具有类型的表中有很多字段。我不想为每个字段手动编写此查询，我想要一些自动化来为我完成timestamptz
0赞
Dean Van Greunen
9/29/2022
where 子句会将其应用于该表中的所有内容。您需要做的就是为每个查询运行 1 个查询table
1赞
Vasilii Rogin
9/29/2022
#2
我使用这个SQL查询解决了它：
--
-- This SQL query shift all timestamptz fields in the database
--
--
BEGIN;
DO $$
declare
sql_query text;
table_row record;
column_row record;
trigger_row record;
BEGIN
FOR table_row IN (
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE' AND table_schema = 'public'
) LOOP
sql_query := '';
RAISE NOTICE 'Checking %', table_row.table_name;
FOR column_row IN (
SELECT column_name
FROM information_schema.columns
WHERE
table_schema = table_row.table_schema
AND table_name = table_row.table_name
AND udt_name = 'timestamptz'
AND is_updatable = 'YES'
) LOOP
sql_query := sql_query ||
'"' || column_row.column_name || '" = "' || column_row.column_name || '" + interval ''100'' day,';
END LOOP;
IF sql_query != '' THEN
sql_query := substr(sql_query,1, length(sql_query)-1); -- Remove last ","
sql_query := 'UPDATE ' || table_row.table_schema || '.' || table_row.table_name || ' SET ' || sql_query || ';';
-- There might be some triggers which so let's disable them before update
FOR trigger_row IN (
SELECT trigger_name FROM information_schema.triggers WHERE
trigger_schema = table_row.table_schema
AND event_object_table = table_row.table_name
AND event_manipulation = 'UPDATE' and
(action_timing = 'BEFORE' or action_timing = 'AFTER')
) LOOP
sql_query := 'alter table ' || table_row.table_schema || '.' || table_row.table_name ||
' disable trigger ' || trigger_row.trigger_name || ';' ||
sql_query ||
'alter table ' || table_row.table_schema || '.' || table_row.table_name ||
' enable trigger ' || trigger_row.trigger_name || ';';
END LOOP;
-- Same for the row level security, disable it if it was enabled
IF (SELECT pg_class.oid FROM pg_class
LEFT JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.oid = pg_class.relnamespace
WHERE relname = table_row.table_name AND
pg_catalog.pg_namespace.nspname = table_row.table_schema AND relrowsecurity) IS NOT NULL THEN
sql_query := 'alter table ' || table_row.table_schema || '.' || table_row.table_name ||
' disable row level security;' ||
sql_query ||
'alter table ' || table_row.table_schema || '.' || table_row.table_name ||
' enable row level security;';
END IF;
RAISE NOTICE ' %', sql_query;
EXECUTE sql_query;
RAISE NOTICE '---------------------------';
END IF;
END LOOP;
END$$;
COMMIT;
评论