PostgreSQL中使用\COPY命令导入CSV时出现语法错误求助

问题根源 #

报错的核心原因是psql的元命令\COPY不支持无续行符的多行写法：

• 当你把\COPY拆分成多行且未添加续行标记时，psql会将第一行视为不完整的元命令，抛出parse error at end of line；
• 后续的FROM行被当作独立SQL语句，触发syntax error at or near 'FROM'。

另外，CSV文件路径存在冗余：tablequery.sql和CSV文件同属setup/psql目录，使用相对路径'budgets.csv'比'setup/psql/budgets.csv'更可靠（避免执行脚本时工作目录差异导致路径找不到）。

解决方案 #

方式1：将\COPY命令合并为单行

修正后的tablequery.sql代码：

-- copy data from budgets.csv file into the budgets table
\COPY budgets (budget_id, dt_create, dt_update, dt_value, category, amount) FROM 'budgets.csv' WITH (FORMAT CSV, HEADER true, DELIMITER ';');

-- copy data from expenses.csv file into the expenses table
\COPY expenses (ulid_id, dt_create, dt_update, dt_value, amount, description, budget_id, category) FROM 'expenses.csv' WITH (FORMAT CSV, HEADER true, DELIMITER ';');

-- copy data from revenues.csv file into the revenues table
\COPY revenues (ulid_id, dt_create, dt_update, dt_value, amount, description) FROM 'revenues.csv' WITH (FORMAT CSV, HEADER true, DELIMITER ';');

方式2：添加续行符\保留多行格式

若希望保持代码可读性，在每行末尾添加反斜杠\标记续行：

-- copy data from budgets.csv file into the budgets table
\COPY budgets (budget_id, dt_create, dt_update, dt_value, category, amount) \
FROM 'budgets.csv' \
WITH (FORMAT CSV, HEADER true, DELIMITER ';');

-- copy data from expenses.csv file into the expenses table
\COPY expenses (ulid_id, dt_create, dt_update, dt_value, amount, description, budget_id, category) \
FROM 'expenses.csv' \
WITH (FORMAT CSV, HEADER true, DELIMITER ';');

-- copy data from revenues.csv file into the revenues table
\COPY revenues (ulid_id, dt_create, dt_update, dt_value, amount, description) \
FROM 'revenues.csv' \
WITH (FORMAT CSV, HEADER true, DELIMITER ';');

额外验证点 #

• 执行脚本时确保工作目录正确，或直接使用绝对路径指向CSV文件；
• 确认CSV文件的行尾格式（LF/CRLF）与psql运行环境兼容，避免隐性解析问题。

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