如何在Redshift中动态基于原表主键列创建无数据新表？

问题原因 #

你之前使用information_schema视图结合LISTAGG时出现报错，是因为Redshift对information_schema视图底层依赖的某些数组类型（如smallint[]）支持有限，导致聚合操作失败。

可行解决方案 #

1. 正确获取主键列列表 #

改用Redshift基于PostgreSQL的系统表pg_catalog来查询主键列，避免information_schema的兼容性问题：

SELECT a.attname AS column_name
FROM pg_catalog.pg_constraint c
JOIN pg_catalog.pg_class t ON c.conrelid = t.oid
JOIN pg_catalog.pg_namespace s ON t.relnamespace = s.oid
JOIN pg_catalog.pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(c.conkey)
WHERE s.nspname = 'MY_SCHEMA'
  AND t.relname = 'MY_TABLE'
  AND c.contype = 'p'
ORDER BY a.attnum;

2. 聚合主键列名并生成动态建表语句 #

使用LISTAGG聚合列名，再通过动态SQL生成空表：

DO $$
DECLARE
  pk_columns TEXT;
BEGIN
  -- 聚合主键列名
  SELECT LISTAGG(a.attname, ', ') WITHIN GROUP (ORDER BY a.attnum)
  INTO pk_columns
  FROM pg_catalog.pg_constraint c
  JOIN pg_catalog.pg_class t ON c.conrelid = t.oid
  JOIN pg_catalog.pg_namespace s ON t.relnamespace = s.oid
  JOIN pg_catalog.pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(c.conkey)
  WHERE s.nspname = 'MY_SCHEMA'
    AND t.relname = 'MY_TABLE'
    AND c.contype = 'p';

  -- 动态执行建表语句
  EXECUTE format('CREATE TABLE new_table AS SELECT %s FROM MY_SCHEMA.MY_TABLE LIMIT 0', pk_columns);
END $$;

3. 手动拼接（如果不需要完全自动化） #

如果不想用PL/pgSQL，可先执行以下查询获取主键列字符串：

SELECT LISTAGG(a.attname, ', ') WITHIN GROUP (ORDER BY a.attnum) AS primary_key_columns
FROM pg_catalog.pg_constraint c
JOIN pg_catalog.pg_class t ON c.conrelid = t.oid
JOIN pg_catalog.pg_namespace s ON t.relnamespace = s.oid
JOIN pg_catalog.pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(c.conkey)
WHERE s.nspname = 'MY_SCHEMA'
  AND t.relname = 'MY_TABLE'
  AND c.contype = 'p';

拿到结果后手动拼接建表语句：

CREATE TABLE new_table AS
SELECT [替换为查询得到的列名]
FROM MY_SCHEMA.MY_TABLE
LIMIT 0;

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