PostgreSQL中CSV特殊字符乱码问题及UTF-8-BOM转换方案咨询

原因分析 #

你遇到的乱码问题源于Excel默认读取纯UTF-8编码的CSV时，会误判为ANSI编码，导致带重音的特殊字符（如Ó）显示异常。添加UTF-8 BOM或设置正确的MIME类型可解决该问题。

解决方案1：添加UTF-8 BOM到CSV内容 #

在生成CSV的开头插入UTF-8 BOM字节序列（EF BB BF），PostgreSQL中可通过CHR(239) || CHR(187) || CHR(191)生成该序列，拼接到CSV头部之前。

修改原函数中sub_arquivo CTE里的CONCAT部分：

CONCAT(
    -- 添加UTF-8 BOM
    CHR(239) || CHR(187) || CHR(191),
    E'Data Cotacao;Fornecedor;Produto;Empresa;Tipo Frete;Preco;Frete;Valor Unitario\n'
    , ARRAY_TO_STRING(ARRAY_AGG(
        CONCAT_WS(';' 
                , TO_CHAR(vc.data_cotacao, 'DD/MM/YYYY') 
                , TRIM(vc.fornecedor)
                , vc.produto 
                , vc.empresa 
                , vc.tipo_frete 
                , TRIM(vc.preco_unitario)
                , vc.valor_frete
                , vc.valor_unitario
        )
    ), E'\n')
)

解决方案2：设置正确的附件MIME类型（更优） #

将附件的type从text/plain改为text/csv; charset=utf-8，明确告知客户端这是UTF-8编码的CSV文件，部分Excel版本无需BOM即可正确识别。

修改arquivo_json中的type字段：

JSON_BUILD_OBJECT(
    'type', 'text/csv; charset=utf-8'
    , 'filename', sub.nome_arquivo 
    , 'content',  sub.conteudo_arquivo
    , 'disposition', 'attachment'
)

组合方案（兼容新旧Excel版本） #

同时添加BOM和设置正确的MIME类型，确保所有Excel版本都能正常显示特殊字符。修改后的完整sub_arquivos部分如下：

WITH sub_arquivos AS (
    WITH sub_arquivo AS (
        SELECT 
            CONCAT(TO_CHAR(CURRENT_TIMESTAMP, 'YYYYMMDD_HH24MI'), '.csv') nome_arquivo,
             TRANSLATE(ENCODE(CONVERT_TO(
                CONCAT(
                    -- 添加UTF-8 BOM
                    CHR(239) || CHR(187) || CHR(191),
                    E'Data Cotacao;Fornecedor;Produto;Empresa;Tipo Frete;Preco;Frete;Valor Unitario\n'
                    , ARRAY_TO_STRING(ARRAY_AGG(
                        CONCAT_WS(';' 
                                , TO_CHAR(vc.data_cotacao, 'DD/MM/YYYY') 
                                , TRIM(vc.fornecedor)
                                , vc.produto 
                                , vc.empresa 
                                , vc.tipo_frete 
                                , TRIM(vc.preco_unitario)
                                , vc.valor_frete
                                , vc.valor_unitario
                        )
                    ), E'\n')
                ), 'UTF8'), 'base64'), E'\n', '') conteudo_arquivo  
        FROM vw_cotacao vc
        WHERE vc.data_cotacao = PDataCotacao
        AND (vc.id_empresa = ANY(PIdEmpresa) OR PIdEmpresa[1] = 0)
    )
    SELECT
        JSON_BUILD_OBJECT(
            'type', 'text/csv; charset=utf-8'
            , 'filename', sub.nome_arquivo 
            , 'content',  sub.conteudo_arquivo
            , 'disposition', 'attachment'
        ) arquivo_json
    FROM    sub_arquivo sub
),

额外注意点 #

• 确保数据库中存储的vc.produto等字段本身为UTF-8编码（PostgreSQL默认数据库编码为UTF8，通常满足要求）。
• 拼接CSV时避免使用可能破坏编码的函数，确保所有字符串操作保持UTF-8一致性。

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