Node.js操作PostgreSQL如何查询排除指定字段外的全表数据

你之前尝试的CTE写法最终只返回字段名字符串，本质原因是原生静态SQL不支持运行时动态裁剪查询列：SQL的查询列必须在语句解析阶段就确定，运行时生成的字符串值不会被自动识别为查询列做替换。
以下是不同场景下可直接落地的方案，不需要手动枚举全字段、也不需要创建视图：

方案1：psql交互场景最简方案（无需创建对象） #

用psql自带的\gexec元命令即可，这个命令会把当前查询返回的字符串结果直接作为SQL语句发送到服务端执行，不需要写函数、建视图：

SELECT format('SELECT %s FROM orders',
  array_to_string(ARRAY(
    SELECT quote_ident(c.column_name)
    FROM information_schema.columns c
    WHERE table_name = 'orders'
      AND table_schema = current_schema()
      AND c.column_name NOT IN ('Quantity')
  ), ',')
);
\gexec

注意这里用quote_ident()包裹字段名，可以避免字段名包含大小写、特殊字符时出现语法错误，加上table_schema = current_schema()条件可以避免匹配到其他schema下的同名orders表。执行完上述语句，会直接返回排除Quantity字段后的全表数据。

方案2：Node.js业务场景适配方案 #

在Node.js服务端操作数据库时，不需要每次查询都动态拼接字段，只需要在服务启动、或者表结构变更时拉取一次过滤后的字段列表缓存即可，性能和手写全字段查询没有差异：

// 以node-postgres驱动为例
const pgClient = /* 你的数据库连接实例 */
const EXCLUDE_FIELDS = ['Quantity'];
// 初始化时拉取字段列表并缓存
async function initQueryField() {
  const { rows } = await pgClient.query(`
    SELECT column_name FROM information_schema.columns
    WHERE table_name = 'orders'
      AND table_schema = current_schema()
      AND column_name <> ALL($1)
  `, [EXCLUDE_FIELDS]);
  return rows.map(col => `"${col.column_name}"`).join(',');
}
// 缓存字段列表，后续查询直接复用
const orderQueryFields = await initQueryField();
// 实际业务查询
async function getOrders() {
  const { rows } = await pgClient.query(`SELECT ${orderQueryFields} FROM orders`);
  return rows;
}

方案3：高频动态查询场景通用函数 #

如果需要在SQL层面频繁做排除字段的查询，可以创建一个通用PL/pgSQL函数，传入表名和要排除的字段即可返回对应结果：

CREATE OR REPLACE FUNCTION select_exclude_columns(
  target_table regclass,
  exclude_cols text[]
) RETURNS SETOF record AS $$
DECLARE
  query_sql text;
BEGIN
  SELECT format('SELECT %s FROM %s',
    array_to_string(ARRAY(
      SELECT quote_ident(attname)
      FROM pg_attribute
      WHERE attrelid = target_table
        AND attnum > 0 -- 跳过系统隐藏列
        AND NOT attisdropped -- 跳过已删除字段
        AND attname <> ALL(exclude_cols)
    ), ','),
    target_table
  ) INTO query_sql;
  RETURN QUERY EXECUTE query_sql;
END;
$$ LANGUAGE plpgsql STABLE;

调用时需要声明返回结果的结构，示例：

SELECT * FROM select_exclude_columns('orders', ARRAY['Quantity'])
AS t(
  id int,
  order_sn text,
  user_id int,
  total_amount numeric,
  create_time timestamptz
  -- 按实际表结构补充除排除字段外的列定义即可
);

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