SQLite转PostgreSQL后Django原生SQL报missing FROM-clause错误

代码示例 #

joined_data = HistoryTable.objects.raw(
        '''
        SELECT pages_historytable.id, pages_historytable.dial, pages_historytable.request_status, pages_historytable.summary, pages_historytable.registration_date, dashboard_storesemails.emails, dashboard_storescode.agent_name
        FROM pages_historytable
        LEFT JOIN dashboard_storescode
        ON pages_historytable.dial = dashboard_storescode.dial
        LEFT JOIN dashboard_storesmails
        ON dashboard_storescode.shop_code = dashboard_storesmails.dtscode
        WHERE pages_historytable.request_status = 'Rejected' AND pages_historytable.Today_Date =  %s;
        
        ''',[today])

错误信息 #

missing FROM-clause entry for table "dashboard_storesemails"
LINE 2: ...le.summary, pages_historytable.registration_date, dashboard_...

问题原因 #

PostgreSQL对SQL标识符（表名、列名）的拼写检查远严于SQLite：

• SELECT语句中引用了dashboard_storesemails.emails，但实际通过LEFT JOIN关联的表是dashboard_storesmails，二者拼写不一致。
• SQLite默认允许这种未在FROM/JOIN中声明的表引用（会返回NULL值），但PostgreSQL会直接抛出“缺少FROM子句条目”的错误。

解决方案 #

根据实际业务需求二选一：

方案1：修正JOIN的表名（实际要关联的是dashboard_storesemails） #

joined_data = HistoryTable.objects.raw(
        '''
        SELECT pages_historytable.id, pages_historytable.dial, pages_historytable.request_status, pages_historytable.summary, pages_historytable.registration_date, dashboard_storesemails.emails, dashboard_storescode.agent_name
        FROM pages_historytable
        LEFT JOIN dashboard_storescode
        ON pages_historytable.dial = dashboard_storescode.dial
        LEFT JOIN dashboard_storesemails
        ON dashboard_storescode.shop_code = dashboard_storesemails.dtscode
        WHERE pages_historytable.request_status = 'Rejected' AND pages_historytable.Today_Date =  %s;
        
        ''',[today])

方案2：修正SELECT中的表引用（实际应为dashboard_storesmails） #

joined_data = HistoryTable.objects.raw(
        '''
        SELECT pages_historytable.id, pages_historytable.dial, pages_historytable.request_status, pages_historytable.summary, pages_historytable.registration_date, dashboard_storesmails.emails, dashboard_storescode.agent_name
        FROM pages_historytable
        LEFT JOIN dashboard_storescode
        ON pages_historytable.dial = dashboard_storescode.dial
        LEFT JOIN dashboard_storesmails
        ON dashboard_storescode.shop_code = dashboard_storesmails.dtscode
        WHERE pages_historytable.request_status = 'Rejected' AND pages_historytable.Today_Date =  %s;
        
        ''',[today])

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