PostgreSQL复制数据时列类型被重置引发长度超限错误排查

我在名为dbo的自定义非默认Schema中创建表并从CSV导入数据，最初列类型设为Varchar(64)时出现以下错误：

psycopg2.errors.StringDataRightTruncation: value too long for type character varying(64)
CONTEXT: COPY fngenderguessallprospssinglebatchv1, line 5396, column firstname: "Unsub-Ylvsccfmmlvnsclscdfhgdhvnrswzfrrbsfrffsasdfefasdfccqwtsb@wherever.Com"

之后我先后把列类型改成Varchar(200)、Varchar、Text，但错误依旧，还是提示列类型为Varchar(64)。为什么表结构修改没生效？

建表代码 #

import psycopg2
from psycopg2 import sql
import os
import glob

connection = psycopg2.connect('postgresql://porter_test2:porter_test2@localhost:5432/porter_test2')
print("Establishing database connection")

db_schema_name = "dbo"

csvPath = r"D:\work_files\porter_project\demo_setup\csv_files"
csvPath = csvPath + "\\"

# Loop through each CSV
for file_name in glob.glob(csvPath+"*.csv"):
    
    table_name = file_name.replace(csvPath, "").replace(".csv", "")
    if len(db_schema_name) > 0:
        table_name = db_schema_name + "." + table_name
    
    # Open file
    fileInput = open(file_name, "r")

    # Extract first line of file
    firstLine = fileInput.readline().strip()

    # Split columns into an array [...]
    columns = firstLine.split(",")

    sqlQueryCreate = 'DROP TABLE IF EXISTS ' + table_name + ";\n"
    sqlQueryCreate += 'CREATE TABLE ' + table_name + "("

    # Define columns for table
    for column in columns:
        #sqlQueryCreate += column + " VARCHAR,\n"
        sqlQueryCreate += column + " TEXT,\n"

    sqlQueryCreate = sqlQueryCreate[:-2]
    sqlQueryCreate += ");"

    cursor = connection.cursor()
    cursor.execute(sqlQueryCreate)
    connection.commit()
    cursor.close()

connection.close()

导入数据代码（错误发生处） #

import psycopg2
from psycopg2 import sql
import os
import glob
import csv

connection = psycopg2.connect('postgresql://porter_test2:porter_test2@localhost:5432/porter_test2')
print("Establishing database connection")

# specify database schema name
db_schema_name = "dbo"

csvPath = r"D:\work_files\porter_project\demo_setup\csv_files"

csvPath = csvPath + "\\"

# Loop through each CS
for file_name in glob.glob(csvPath+"*.csv"):
    table_name = file_name.replace(csvPath, "").replace(".csv", "")

    copy_sql = "COPY {table_name} FROM '{file}' WITH CSV HEADER DELIMITER as ',';".format(
        table_name = table_name,
        file = file_name,
    )

    with open(file_name, 'r') as f:
        cursor = connection.cursor()
        cursor.copy_expert(sql=copy_sql, file=f)
        connection.commit()
        cursor.close()

connection.close()

原因及解决方案 #

核心原因 #

你操作的根本不是同一张表：

• 建表代码会给表名加上dbo.前缀，最终在dboSchema下创建了目标表，且修改后的结构是正确的
• 但导入数据的代码里，生成的table_name没有加dbo.前缀，PostgreSQL会默认使用publicSchema下的同名旧表——这张表还是你最初创建的、列类型为Varchar(64)的版本，所以报错不会消失。

修复步骤 #

1. 修正导入代码的表名逻辑，和建表代码保持一致，给表名加上Schema前缀：

   table_name = file_name.replace(csvPath, "").replace(".csv", "")
   # 新增这两行
   if len(db_schema_name) > 0:
       table_name = db_schema_name + "." + table_name
   

2. 建议优化SQL拼接方式，用psycopg2.sql模块避免SQL注入风险，同时兼容特殊命名的表/列：

   • 建表代码修改SQL生成部分：

     # 拆分Schema和表名
     schema_table = table_name.split('.')
     sqlQueryCreate = sql.SQL('DROP TABLE IF EXISTS {};\nCREATE TABLE {} ({})').format(
         sql.Identifier(schema_table[0], schema_table[1]),
         sql.Identifier(schema_table[0], schema_table[1]),
         sql.SQL(', ').join([sql.SQL('{} TEXT').format(sql.Identifier(col)) for col in columns])
     )
     

   • 导入代码修改copy_sql生成：

     copy_sql = sql.SQL("COPY {} FROM %s WITH CSV HEADER DELIMITER as ',';").format(
         sql.Identifier(db_schema_name, table_name)
     )
     # 用vars参数传递文件名，避免SQL注入
     cursor.copy_expert(sql=copy_sql, file=f, vars=(file_name,))
     

3. 重新运行建表代码确认表结构正确，再执行导入代码即可解决问题。

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