PostgreSQL JOIN查询使用别名与驼峰列名时报错列不存在

问题 #

我用C#的Npgsql库连接Supabase提供的PostgreSQL数据库，因限制无法使用官方SDK，其他查询均正常，但这段JOIN查询始终报错：

public ChapterQuestion Read(ChapterQuestion itemToRead, List<QuestionOption> questionOptions)
{
    ChapterQuestion chapterQuestion = null;

    var id = itemToRead.Id;
    var subjectId = itemToRead.SubjectId;
    var chapterId = itemToRead.ChapterId;
    var title = itemToRead.Title;
    var answerValue = itemToRead.AnswerValue;
    var shortTitle = title.Length > 20 ? title[..20] : title;

    // Verificar si existe el mismo registro
    string subjectIdString = (subjectId == null) ? $"WHERE @subjectId IS NULL AND " : $"WHERE \"subjectId\" = @subjectId AND ";
    string chapterIdString = (chapterId == null) ? $"@chapterId IS NULL AND " : $"\"chapterId\" = @chapterId AND ";
    string questionOptionDescriptionsString = string.Join(", ", questionOptions.Select(qo => $"'{qo.Description.Replace("'", "''")}'"));

    string selectQuery = $@"
        SELECT DISTINCT question.* 
        FROM {{TABLE_CHAPTER_QUESTION}} AS question
        JOIN {{SupabaseQuestionOptionRepository.TABLE_QUESTION_OPTION}} AS qo ON question.id = ""qo.chapterQuestionId"""
        {{subjectIdString}}
        {{chapterIdString}}
        title = @title AND
        ""answerValue"" = @answerValue
        AND qo.description IN ({questionOptionDescriptionsString})";

    using (NpgsqlCommand selectCommand = new NpgsqlCommand(selectQuery, Connection))
    {
        selectCommand.Parameters.AddWithValue("@subjectId", subjectId);
        selectCommand.Parameters.AddWithValue("@chapterId", chapterId);
        selectCommand.Parameters.AddWithValue("@title", title);
        selectCommand.Parameters.AddWithValue("@answerValue", answerValue);

        var result = selectCommand.ExecuteScalar();

        if (result != null)
        {
            using NpgsqlDataReader reader = selectCommand.ExecuteReader();
            if (reader.Read())
            {
                int idOrdinal = reader.GetOrdinal("Id");
                int chapterQuestionId = reader.GetInt32(idOrdinal);
                LoggerService.Log($"The '{nameof(ChapterQuestion)}' record with the title '{shortTitle}' and subjectId '{subjectId}' and chapterId '{chapterId}' and answerValue '{answerValue}', exists with ID {chapterQuestionId}");
                chapterQuestion = new ChapterQuestion(chapterQuestionId, itemToRead);
            }
            else
            {
                LoggerService.Log($"The '{nameof(ChapterQuestion)}' record with the title '{shortTitle}' and subjectId '{subjectId}' and chapterId '{chapterId}' and answerValue '{answerValue}' was not found.");
                reader.Close();
            }
            CloseConnection();
            return chapterQuestion;
        }
        else
        {
            LoggerService.Log($"The '{nameof(ChapterQuestion)}' record with the title '{shortTitle}' and subjectId '{subjectId}' and chapterId '{chapterId}' and answerValue '{answerValue}' was not found.");
        }
    }

    CloseConnection();

    return chapterQuestion;
}

报错信息：

column "qo.chapterQuestionId" does not exist.

去掉双引号后报错变为：

column qo.chapterquestionid does not exist.

已确认数据库中存在chapterQuestionId列，请问问题出在哪？如何修改才能正常执行？

原因分析 #

1. 双引号使用错误：PostgreSQL中双引号用于包裹区分大小写的标识符（比如驼峰命名的列名）。你把qo.chapterQuestionId整个用双引号括起来，PostgreSQL会将其识别为一个完整的列名（即列名叫qo.chapterQuestionId），而非qo表下的chapterQuestionId列，因此提示列不存在。
2. 大小写匹配问题：如果不使用双引号，PostgreSQL会自动将标识符转为小写，而你的实际列名是驼峰格式的chapterQuestionId，转小写后变为chapterquestionid，与数据库中的列名不匹配，因此同样报错。
3. 此外代码还有两个潜在问题：
   • WHERE子句拼接逻辑错误：若subjectId和chapterId均不为空，会生成WHERE ... AND WHERE ...的非法SQL语句。
   • 直接拼接questionOptionDescriptionsString存在SQL注入风险，且易因特殊字符导致语法错误。

修复方案 #

1. 修正JOIN的ON条件 #

将双引号仅包裹列名本身，正确写法：

JOIN {SupabaseQuestionOptionRepository.TABLE_QUESTION_OPTION} AS qo ON question.id = qo."chapterQuestionId"

2. 修正WHERE子句拼接逻辑 #

不要将WHERE关键字嵌入每个条件，先收集所有条件再统一处理：

List<string> conditions = new List<string>();
if (subjectId == null)
{
    conditions.Add("@subjectId IS NULL");
}
else
{
    conditions.Add("\"subjectId\" = @subjectId");
}

if (chapterId == null)
{
    conditions.Add("@chapterId IS NULL");
}
else
{
    conditions.Add("\"chapterId\" = @chapterId");
}

conditions.Add("title = @title");
conditions.Add("\"answerValue\" = @answerValue");

3. 参数化处理选项描述，避免SQL注入 #

将每个选项描述作为参数添加，而非直接拼接字符串：

// 生成参数占位符
string optionPlaceholders = string.Join(", ", Enumerable.Range(0, questionOptions.Count).Select(i => $"@option{i}"));
conditions.Add($"qo.description IN ({optionPlaceholders})");

// 拼接WHERE子句
string whereClause = conditions.Any() ? $"WHERE {string.Join(" AND ", conditions)}" : "";

4. 修正查询执行逻辑 #

移除重复执行的ExecuteScalar，直接使用ExecuteReader读取结果：

完整修正后的核心代码片段 #

List<string> conditions = new List<string>();
if (subjectId == null)
{
    conditions.Add("@subjectId IS NULL");
}
else
{
    conditions.Add("\"subjectId\" = @subjectId");
}

if (chapterId == null)
{
    conditions.Add("@chapterId IS NULL");
}
else
{
    conditions.Add("\"chapterId\" = @chapterId");
}

conditions.Add("title = @title");
conditions.Add("\"answerValue\" = @answerValue");

// 参数化选项描述
string optionPlaceholders = string.Join(", ", Enumerable.Range(0, questionOptions.Count).Select(i => $"@option{i}"));
conditions.Add($"qo.description IN ({optionPlaceholders})");

string whereClause = conditions.Any() ? $"WHERE {string.Join(" AND ", conditions)}" : "";

string selectQuery = $@"
    SELECT DISTINCT question.* 
    FROM {TABLE_CHAPTER_QUESTION} AS question
    JOIN {SupabaseQuestionOptionRepository.TABLE_QUESTION_OPTION} AS qo ON question.id = qo.""chapterQuestionId""
    {whereClause}";

using (NpgsqlCommand selectCommand = new NpgsqlCommand(selectQuery, Connection))
{
    selectCommand.Parameters.AddWithValue("@subjectId", subjectId ?? DBNull.Value);
    selectCommand.Parameters.AddWithValue("@chapterId", chapterId ?? DBNull.Value);
    selectCommand.Parameters.AddWithValue("@title", title);
    selectCommand.Parameters.AddWithValue("@answerValue", answerValue);

    // 添加选项参数
    for (int i = 0; i < questionOptions.Count; i++)
    {
        selectCommand.Parameters.AddWithValue($"@option{i}", questionOptions[i].Description);
    }

    using NpgsqlDataReader reader = selectCommand.ExecuteReader();
    if (reader.Read())
    {
        int idOrdinal = reader.GetOrdinal("Id");
        int chapterQuestionId = reader.GetInt32(idOrdinal);
        LoggerService.Log($"The '{nameof(ChapterQuestion)}' record with the title '{shortTitle}' and subjectId '{subjectId}' and chapterId '{chapterId}' and answerValue '{answerValue}', exists with ID {chapterQuestionId}");
        chapterQuestion = new ChapterQuestion(chapterQuestionId, itemToRead);
    }
    else
    {
        LoggerService.Log($"The '{nameof(ChapterQuestion)}' record with the title '{shortTitle}' and subjectId '{subjectId}' and chapterId '{chapterId}' and answerValue '{answerValue}' was not found.");
    }
}

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