PostgreSQL获取各医院最新更新行报错求助

报错原因 #

你遇到的错误本质是PostgreSQL标识符大小写敏感导致的：
在内层子查询中，你给hospitalName等列添加了带双引号的别名"hospitalName"，而PostgreSQL对带双引号的标识符会严格区分大小写。外层引用时使用LatestRecords.hospitalName（全小写），与实际定义的大写开头别名"hospitalName"不匹配，因此数据库找不到对应列。

另外注意：如果你的需求是按每家医院分组取最新记录，原SQL中PARTITION BY facilityName可能是笔误，应该改为PARTITION BY hospitalName，否则会按facilityName分组，不符合业务逻辑。

修正后的SQL方案 #

方案一：去掉不必要的列别名（推荐）

直接保留原列名，避免大小写匹配问题：

query = f"""
        SELECT to_json(r)
        FROM (
            SELECT hospitalName, lastUpdatedOn, active
            FROM (
                SELECT 
                    hospitalName, 
                    lastUpdatedOn, 
                    active, 
                    ROW_NUMBER() OVER (PARTITION BY hospitalName ORDER BY lastUpdatedOn DESC) AS rn
                FROM hospitals
            ) AS LatestRecords
            WHERE rn = 1
        ) AS r;
    """

方案二：保持带引号别名的一致性

如果必须使用带引号的别名，外层引用时需严格匹配大小写：

query = f"""
        SELECT to_json(r)
        FROM (
            SELECT LatestRecords."hospitalName" AS "hospitalName", LatestRecords."lastUpdatedOn" AS "lastUpdatedOn", LatestRecords."active" AS "active"
            FROM (
                SELECT hospitalName AS "hospitalName", lastUpdatedOn AS "lastUpdatedOn", active AS "active", ROW_NUMBER() OVER (PARTITION BY hospitalName ORDER BY lastUpdatedOn DESC) AS rn
                FROM hospitals
            ) AS LatestRecords
            WHERE rn = 1
        ) AS r;
    """

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