如何正确访问psql通过-v选项传入的变量？报错解决

问题场景 #

通过bash执行psql命令时，已通过-v job_exec_uid=${JOB_UID}传入整数类型变量：

psql -q -d $DB --echo-all --set ON_ERROR_STOP=on -f $APP_BASE_PATH/$PROJ/$SCRIPT_NAME -v job_end_bndry_ts="${job_end_bndry_ts}" -v job_exec_uid=${JOB_UID}  > ${LOGPATH}/${MOD_NM}_${LOGTIME}.log 2> >(tee -a $errmsg >&1)

但在SQL脚本中使用该变量时触发错误：ERROR: invalid input syntax for integer: "job_exec_uid"，出错的SQL片段如下：

INSERT INTO schema.staging_table
(
JSON_DATAREC_FL,
JOBEXEC_UID,
JSON_FL_ID,
INSRT_TS
)
SELECT
 json_data AS JSON_DATA,
 'job_exec_uid'::integer AS JOBEXEC_UID,
(JSON_DATA::json#>>'{refNumber}') AS JSON_FILE_ID,
now() INSRT_TS
FROM json_ext
;

目标表schema.staging_table的JOBEXEC_UID字段类型为integer。

原因分析 #

SQL中把job_exec_uid用单引号包裹，PostgreSQL会将其识别为字符串字面量，而非psql传入的变量。当尝试将字符串"job_exec_uid"转换为整数类型时，必然触发语法错误。

正确的变量访问方式 #

psql通过-v传入的变量，在SQL中需要使用:前缀直接引用，不需要额外加引号（变量值本身是整数类型，可直接赋值）。修改后的SQL片段：

INSERT INTO schema.staging_table
(
JSON_DATAREC_FL,
JOBEXEC_UID,
JSON_FL_ID,
INSRT_TS
)
SELECT
 json_data AS JSON_DATA,
 :job_exec_uid AS JOBEXEC_UID,
(JSON_DATA::json#>>'{refNumber}') AS JSON_FILE_ID,
now() INSRT_TS
FROM json_ext
;

如果需要强制确保类型匹配，也可以显式指定类型转换：

:job_exec_uid::integer AS JOBEXEC_UID

另外，bash中传入变量时，若JOB_UID可能包含特殊字符，建议用双引号包裹避免解析异常：

-v job_exec_uid="${JOB_UID}"

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