PostgreSQL如何检测字段是否为Base64编码？

不需要自行编写try_decode函数，完全可以通过正则匹配+条件判断来区分字段内容是原始二进制还是Base64编码，再用CASE WHEN分支处理。

核心思路 #

Base64编码有明确的格式特征：

• 仅包含大小写字母、数字、+、/，结尾可能有1-2个=作为填充
• 有效编码的长度是4的倍数（算上填充=）

而原始二进制数据（比如PDF的%PDF-1.4、JPEG的\xFF\xD8\xFF）通常包含Base64字符集外的字符，或者解码后能匹配对应文件的签名，以此可以区分两者。

具体SQL实现示例 #

SELECT
  id,
  CASE
    -- 第一步：匹配Base64的格式规则
    WHEN piece_jointe ~ '^[A-Za-z0-9+/]+={0,2}$'
         -- 第二步：解码后验证文件签名，降低误判概率（以PDF、JPEG为例）
         AND (
           decode(piece_jointe, 'base64')::text LIKE '%PDF-%'
           OR decode(piece_jointe, 'base64') ~ '^\xFF\xD8\xFF'
         ) THEN decode(piece_jointe, 'base64')
    -- 不符合Base64规则，直接保留原始二进制
    ELSE piece_jointe
  END AS processed_piece_jointe
FROM pieces_jointes;

调整说明 #

1. 如果源数据中的Base64包含换行符，需要修改正则表达式为^[A-Za-z0-9+/\n]+={0,2}$
2. 可以根据实际涉及的文件类型（TIFF、ETF、RTF等）扩展文件签名验证条件：
   • TIFF：解码后开头为\x49\x49\x2A\x00或\x4D\x4D\x00\x2A
   • RTF：解码后开头为{\rtf

这种方式既避免了直接调用decode报错，又能准确区分两种数据格式，满足迁移需求。

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