如何获取Redshift指定数据库中各表的创建及最后修改时间

一、获取表创建时间 #

使用SVV_TABLE_INFO系统视图，它不受STL系列视图的保留时间限制，能直接获取所有用户表的初始创建时间：

SELECT 
  schemaname,
  tablename,
  createdate AS table_create_time
FROM svv_table_info
WHERE schemaname = '你的目标模式名' -- 替换为实际数据库模式
ORDER BY createdate DESC;

注：createdate记录的是表的首次创建时间，后续执行ALTER TABLE等结构修改操作不会改变这个值。

二、获取表最后修改时间（分场景处理） #

Redshift没有原生的长期存储表修改时间的系统视图，需根据需求选择方案：

1. 表结构修改时间（DDL操作） #

如果要追踪ALTER TABLE这类结构变更的时间，短期可以查STL_DDLTEXT，但它仅保留2-5天数据。要长期留存，建议自建DDL日志表并定期同步数据：

• 先创建日志表：

CREATE TABLE ddl_change_log (
  event_time TIMESTAMP,
  username VARCHAR(100),
  schema_name VARCHAR(100),
  table_name VARCHAR(100),
  ddl_command TEXT
);

• 定期执行以下语句同步最近的DDL操作（可设置为定时任务）：

INSERT INTO ddl_change_log
SELECT 
  starttime AS event_time,
  username,
  n.nspname AS schema_name,
  c.relname AS table_name,
  text AS ddl_command
FROM stl_ddltext d
JOIN pg_class c ON d.objid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE starttime > (SELECT COALESCE(MAX(event_time), '1970-01-01') FROM ddl_change_log)
  AND d.text LIKE 'ALTER TABLE%';

• 查询表结构最后修改时间：

SELECT 
  schema_name,
  table_name,
  MAX(event_time) AS last_ddl_modify_time
FROM ddl_change_log
WHERE schema_name = '你的目标模式名'
GROUP BY schema_name, table_name;

2. 表数据修改时间（DML操作） #

如果要追踪INSERT/UPDATE/DELETE这类数据变更的时间，有两种方案：

• 短期查询方案：利用STL视图获取近期数据，但受保留时间限制：

SELECT 
  n.nspname AS schema_name,
  c.relname AS table_name,
  MAX(q.starttime) AS last_dml_modify_time
FROM stl_query q
JOIN stl_querytext t ON q.query = t.query
JOIN pg_class c ON q.relid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE t.text ILIKE '%INSERT INTO%' 
   OR t.text ILIKE '%UPDATE%' 
   OR t.text ILIKE '%DELETE FROM%'
  AND n.nspname = '你的目标模式名'
GROUP BY n.nspname, c.relname;

• 长期可靠方案：在表中自定义字段并通过触发器自动更新，这是最稳定的方式：

-- 给现有表添加字段（新建表可直接包含该字段）
ALTER TABLE your_target_table ADD COLUMN last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

-- 创建触发器函数
CREATE OR REPLACE FUNCTION update_last_updated()
RETURNS TRIGGER AS $$
BEGIN
  NEW.last_updated = CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 绑定触发器到表
CREATE TRIGGER trigger_update_last_updated
BEFORE INSERT OR UPDATE ON your_target_table
FOR EACH ROW EXECUTE FUNCTION update_last_updated();

• 查询数据最后修改时间：

SELECT MAX(last_updated) AS last_data_modify_time FROM your_target_table;

三、注意事项 #

• SVV_TABLE_INFO的createdate仅记录初始创建时间，结构修改不会更新该值；
• STL系列视图仅保留短期数据，长期追踪必须依赖自建日志表或自定义字段方案；
• 触发器方案对现有表需要手动添加字段并绑定触发器，新建表可直接集成。

内容的提问来源于stack exchange，提问作者Amol