PostgreSQL JOIN Query with Aliases using capital letters not working

Question

I'm using C# Npgsql library to make some calls and operations into a PostgreSQL Database (the one from Supabase).

I know that Supabase has it's own SDK, but I can't use it.

So far so good all my queries and methods work, but I cannot figure out how to make this one work:

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;
}

The error that I received is:

column "qo.chapterQuestionId" does not exist.

Triggered by the line:

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

I can confirm that the column exists in my Database:

AFAIK I need to use " " to indicate that this value is case sensitive, I've tried without them and the message turns into:

column qo.chapterquestionid does not exist.

I also tried to remove the "AS" when using the aliases from the query, but it doesn't affect anything.

Why is the database indicating that the column does not exist, if it does? How can I make the query to work properly?

Answer 1

As already answered in comments, you need to quote the column name part only:

qo."chapterQuestionId"

The part before the dot is a table reference or alias. You can quote it as well if you need it to be case-sensitive:

"QO"."chapterQuestionId"

Only include the dot if it is part of the column name:

qo."chapterQuestion.id"