PostgreSQL bytea列转换：将Node Buffer hex编码转回原始字节

纯PostgreSQL 12+数据库方案 #

直接转换更新 #

核心逻辑是先将存储hex字符串的bytea列转成文本格式（得到原始hex字符串），再将该hex字符串解码为原始字节的bytea类型，最后更新回表中。

1. 先备份数据（必做，避免操作失误）

CREATE TABLE your_table_backup AS SELECT * FROM your_table;

2. 执行转换更新
   替换your_table为实际表名，可添加WHERE条件过滤需要转换的行：

UPDATE your_table
SET blob = decode(blob::text, 'hex')
WHERE blob IS NOT NULL;

解释：

• blob::text：把bytea类型转换为文本，此时得到的就是当初Node.js存入的hex格式字符串
• decode(..., 'hex')：将hex字符串解码为原始字节的bytea类型，即目标原始数据

3. 验证转换结果（可选）
   可先查询少量数据确认转换正确性：

SELECT
  blob AS original_bytea,
  blob::text AS stored_hex_string,
  decode(blob::text, 'hex') AS restored_original_bytea
FROM your_table
LIMIT 10;

导出转换再更新方案 #

如果担心直接数据库操作风险，可采用导出-转换-重导入流程：

步骤1：导出数据为CSV #

使用psql导出包含目标列的数据（替换占位符为你的数据库信息）：

psql -U your_db_user -d your_db_name -c "COPY your_table (id, blob) TO '/tmp/your_table_data.csv' WITH (FORMAT CSV, HEADER, ENCODING 'UTF8');"

注：假设id是表的主键，用于后续匹配数据，可根据实际列调整导出字段。

步骤2：用Node.js转换数据 #

安装依赖后，编写脚本将hex字符串转回原始字节，并转为PostgreSQL支持的bytea格式：

npm install csv-parser csv-writer

转换脚本：

const fs = require('fs');
const csv = require('csv-parser');
const createCsvWriter = require('csv-writer').createObjectCsvWriter;

const inputPath = '/tmp/your_table_data.csv';
const outputPath = '/tmp/your_table_data_converted.csv';
const results = [];

fs.createReadStream(inputPath)
  .pipe(csv())
  .on('data', (row) => {
    // 将存储的hex字符串转回原始Buffer
    const originalBuffer = Buffer.from(row.blob, 'hex');
    // 转为PostgreSQL可识别的bytea格式（\x开头的十六进制）
    row.blob = '\\x' + originalBuffer.toString('hex');
    results.push(row);
  })
  .on('end', () => {
    const csvWriter = createCsvWriter({
      path: outputPath,
      header: Object.keys(results[0]).map(key => ({id: key, title: key}))
    });
    csvWriter.writeRecords(results).then(() => console.log('数据转换完成'));
  });

步骤3：重新导入转换后的数据 #

# 先清空原表（需确认已备份）
psql -U your_db_user -d your_db_name -c "TRUNCATE TABLE your_table;"

# 导入转换后的CSV
psql -U your_db_user -d your_db_name -c "COPY your_table (id, blob) FROM '/tmp/your_table_data_converted.csv' WITH (FORMAT CSV, HEADER, ENCODING 'UTF8');"

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