PostgreSQL从CSV导入PostGIS数据：动态路径与几何提取问题

问题②：提取CSV中JSON格式的几何数据并转换为GEOGRAPHY类型 #

你遇到的extra data after last expected column错误，根源是CSV的location字段是包含逗号的JSON字符串，直接用COPY会被误解析为多列。正确做法是通过临时过渡表中转，再提取几何数据转换后插入目标表：

1. 创建两个临时表：一个存储原始行数据，一个存储拆分后的结构化数据

-- 存储原始整行文本，避开字段内逗号的解析问题
CREATE TEMP TABLE temp_raw (line TEXT);
-- 存储拆分后的结构化数据
CREATE TEMP TABLE temp_individuals (
  first_name VARCHAR(255),
  last_name VARCHAR(255),
  location JSONB
);

2. 导入单份CSV到临时行表，再拆分结构化数据

-- 导入CSV
COPY temp_raw FROM 'path/to/individuals.csv';

-- 跳过表头，拆分数据到结构化临时表
INSERT INTO temp_individuals(first_name, last_name, location)
SELECT
  split_part(line, ',', 1) AS first_name,
  split_part(line, ',', 2) AS last_name,
  regexp_replace(line, '^[^,]+,[^,]+,', '')::JSONB AS location
FROM temp_raw
WHERE line NOT LIKE 'first_name,%';

3. 提取几何数据插入目标表
   用ST_GeogFromGeoJSON函数直接将GeoJSON格式的几何对象转换为GEOGRAPHY类型：

INSERT INTO individuals(first_name, last_name, location_point)
SELECT
  first_name,
  last_name,
  ST_GeogFromGeoJSON(location->>'geometry') AS location_point
FROM temp_individuals;

问题①：动态遍历CSV文件并批量导入 #

通过PL/pgSQL函数结合pg_ls_dir获取目录下的CSV文件列表，再用EXECUTE动态生成导入命令，实现批量处理：

CREATE OR REPLACE FUNCTION bulk_import_individuals(p_directory TEXT)
RETURNS VOID AS $$
DECLARE
  v_file TEXT;
  v_file_path TEXT;
BEGIN
  -- 初始化临时表
  CREATE TEMP TABLE IF NOT EXISTS temp_raw (line TEXT);
  CREATE TEMP TABLE IF NOT EXISTS temp_individuals (
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    location JSONB
  );

  -- 遍历目录下所有CSV文件
  FOR v_file IN SELECT filename FROM pg_ls_dir(p_directory) WHERE filename LIKE '%.csv' LOOP
    v_file_path := p_directory || '/' || v_file;
    
    -- 导入当前文件到行表
    EXECUTE format(
      'COPY temp_raw FROM %L TEXT',
      v_file_path
    );
    
    -- 拆分数据到结构化临时表
    INSERT INTO temp_individuals(first_name, last_name, location)
    SELECT
      split_part(line, ',', 1) AS first_name,
      split_part(line, ',', 2) AS last_name,
      regexp_replace(line, '^[^,]+,[^,]+,', '')::JSONB AS location
    FROM temp_raw
    WHERE line NOT LIKE 'first_name,%';
    
    -- 清空行表，准备下一个文件
    TRUNCATE temp_raw;
  END LOOP;
  
  -- 将所有临时数据插入目标表
  INSERT INTO individuals(first_name, last_name, location_point)
  SELECT
    first_name,
    last_name,
    ST_GeogFromGeoJSON(location->>'geometry') AS location_point
  FROM temp_individuals;
  
  -- 清理临时表
  DROP TABLE temp_individuals;
  DROP TABLE temp_raw;
END;
$$ LANGUAGE plpgsql;

调用函数执行批量导入：

SELECT bulk_import_individuals('/path/to/your/csv/directory');

注意事项 #

• 确保PostgreSQL进程拥有目标目录的读取权限，可通过调整目录权限或数据库用户权限实现。
• 若CSV文件表头格式不一致，需修改表头过滤逻辑适配不同文件。
• 针对50万条量级的数据，建议在函数中加入事务控制，或分批处理数据，避免长时间锁表影响其他操作。

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