Laravel中PostgreSQL大写列名SQL查询报错问题求助

问题核心 #

PostgreSQL会自动把未用双引号包裹的标识符（表名、列名）转为小写，而你的实际列名是驼峰式大写（如employeeID），直接写v_patient.employeeid自然找不到对应列。之前用反引号无效是因为反引号是MySQL的标识符包裹方式，PostgreSQL只认双引号。

解决方案 #

1. 修正原生SQL的双引号使用 #

在PHP字符串中，要注意双引号的转义问题：

• 若用双引号定义SQL字符串，内部双引号需转义为\"
• 若用单引号定义SQL字符串，内部双引号无需转义，但单引号内容要转义为\'

修改后的原生SQL示例（单引号定义版）：

$sql = 'SELECT 
            sp.*,
            patient.v_emp_id as badge_number, v_patient.positionTitle as jabatan, v_patient.dpName as tempat_kerja, 
            "v_spv.employeeName" as name_spv, v_spv.phones as telp_spv, "v_patient.todayShiftDesc" as today_shift, "v_patient.tomorrowShiftDesc" as tom_shift,
            (CASE WHEN rp.ifirm_id IS NOT NULL THEN ifirm.ifirm_name ELSE \'UMUM\' END) AS penanggung,
            loc.locid
        FROM surat sp 
        LEFT JOIN reg rp ON rp.regpid = sp.regpid
        LEFT JOIN person patient ON patient.pid = sp.pid
        LEFT JOIN v_emp v_patient ON "v_patient.employeeID" = patient.v_emp_id
        LEFT JOIN v_emp v_spv ON "v_patient.sprEmployeeID" = "v_spv.employeeID"
        LEFT JOIN person doctor ON doctor.pid = sp.doctor_id
        LEFT JOIN insurance_firm ifirm ON rp.ifirm_id = ifirm.ifirm_id
        LEFT JOIN laboratory_order_chem loc ON loc.regpid = rp.regpid
        WHERE
            sp.spid = ?';

return DB::selectOne($sql, [$spid]);

注：这里改用参数绑定?和[$spid]，避免直接拼接变量导致的SQL注入风险。

2. 用Laravel查询构造器推荐 #

查询构造器会自动根据数据库类型处理标识符包裹，完全规避手动转义问题：

return DB::table('surat as sp')
    ->select(
        'sp.*',
        'patient.v_emp_id as badge_number',
        'v_patient.positionTitle as jabatan',
        'v_patient.dpName as tempat_kerja',
        'v_spv.employeeName as name_spv',
        'v_spv.phones as telp_spv',
        'v_patient.todayShiftDesc as today_shift',
        'v_patient.tomorrowShiftDesc as tom_shift',
        DB::raw('(CASE WHEN rp.ifirm_id IS NOT NULL THEN ifirm.ifirm_name ELSE \'UMUM\' END) AS penanggung'),
        'loc.locid'
    )
    ->leftJoin('reg as rp', 'rp.regpid', '=', 'sp.regpid')
    ->leftJoin('person as patient', 'patient.pid', '=', 'sp.pid')
    ->leftJoin('v_emp as v_patient', 'v_patient.employeeID', '=', 'patient.v_emp_id')
    ->leftJoin('v_emp as v_spv', 'v_patient.sprEmployeeID', '=', 'v_spv.employeeID')
    ->leftJoin('person as doctor', 'doctor.pid', '=', 'sp.doctor_id')
    ->leftJoin('insurance_firm as ifirm', 'rp.ifirm_id', '=', 'ifirm.ifirm_id')
    ->leftJoin('laboratory_order_chem as loc', 'loc.regpid', '=', 'rp.regpid')
    ->where('sp.spid', $spid)
    ->first();

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