

PostgreSQLで特定のテーブルJOINのみエラーが出る

Go言語とPostgreSQLでAPIサーバーを開発しています。

その際、userテーブル をJOIN使用とした時のみ、以下のようなエラーが出てしまいます。

questionテーブルを起点にanswer、trip,userをJOINするSELECT文を書いています。

[code]

    {
        "Severity": "ERROR",
        "Code": "42601",
        "Message": "syntax error at or near \".\"",
        "Detail": "",
        "Hint": "",
        "Position": "76",
        "InternalPosition": "",
        "InternalQuery": "",
        "Where": "",
        "Schema": "",
        "Table": "",
        "Column": "",
        "DataTypeName": "",
        "Constraint": "",
        "File": "scan.l",
        "Line": "1149",
        "Routine": "scanner_yyerror"
    }
    
[/code]

ソースコードはGORMで以下のようなものを書いています。

[code]

    func GetQuestionDetail(c *gin.Context) {
        id, err := strconv.Atoi(c.Param("id"))
        if err != nil {
            c.JSON(http.StatusBadRequest, err)
        }
        type Result struct {
            models.Question
            models.Answer
            Id int64 `json:"question_id" db:"id"`
            Title       string    `json:"title" db:"title"`
            PictureUrls string    `json:"picture_urls" db:"picture_urls"`
            Description string    `json:"description" db:"description"`
            Start       time.Time `json:"start" db:"start"`
            PostUserID  int64     `json:"post_user_id" db:"user_id"`
            AnswerUserName string `json:"answer_user_name" db:"name"`
        }
        var result []Result
        if err := config.DB.Table("question").Select("question.*, question.id AS question_id, trip.*, answer.trip_id, user.* ").
            Joins("left outer join answer on answer.question_id = question.id").
            Joins("join trip on trip.trip_id = answer.trip_id").
            Joins("join user on user.user_id = answer.user_id").
            Where("question.id = ?", id).Scan(&result).Error; err != nil {
            c.JSON(http.StatusBadRequest, err)
        } else {
            c.JSON(http.StatusOK, result)
        }
    }
    
[/code]

テーブル構造

**user**

[code]

    -- Table Definition ----------------------------------------------
    
    CREATE TABLE "user" (
        user_id BIGSERIAL PRIMARY KEY,
        name text NOT NULL,
        about text NOT NULL,
        picture_url text NOT NULL,
        email text NOT NULL UNIQUE CHECK (email <> ''::text),
        password text NOT NULL CHECK (password <> ''::text),
        role smallint NOT NULL CHECK (role > 0),
        location text,
        born text NOT NULL,
        familiar text NOT NULL,
        skills text NOT NULL,
        background_url text NOT NULL,
        sns_url text,
        birth smallint,
        languages text NOT NULL,
        gender text,
        timezone text
    );
    
    -- Indices -------------------------------------------------------
    
    CREATE UNIQUE INDEX user_email_key ON "user"(email text_ops);
    CREATE UNIQUE INDEX user_pkey ON "user"(user_id int8_ops);
    
[/code]

**question**

[code]

    -- Table Definition ----------------------------------------------
    
    CREATE TABLE question (
        id SERIAL PRIMARY KEY,
        created_at timestamp with time zone,
        updated_at timestamp with time zone,
        deleted_at timestamp with time zone,
        user_id bigint,
        body text
    );
    
    -- Indices -------------------------------------------------------
    
    CREATE UNIQUE INDEX question_pkey ON question(id int4_ops);
    CREATE INDEX idx_question_deleted_at ON question(deleted_at timestamptz_ops);
    
[/code]

**answer**

[code]

    -- Table Definition ----------------------------------------------
    
    CREATE TABLE answer (
        id SERIAL PRIMARY KEY,
        created_at timestamp with time zone,
        updated_at timestamp with time zone,
        deleted_at timestamp with time zone,
        question_id bigint,
        user_id bigint,
        trip_id bigint
    );
    
    -- Indices -------------------------------------------------------
    
    CREATE UNIQUE INDEX answer_pkey ON answer(id int4_ops);
    CREATE INDEX idx_answer_deleted_at ON answer(deleted_at timestamptz_ops);
    
[/code]

**trip**

[code]

    -- Table Definition ----------------------------------------------
    
    CREATE TABLE trip (
        trip_id BIGSERIAL PRIMARY KEY,
        title text NOT NULL CHECK (title <> ''::text),
        description text NOT NULL,
        notes text NOT NULL,
        min_participants integer NOT NULL,
        max_participants integer NOT NULL,
        price_per_person real NOT NULL CHECK (price_per_person >= 0::double precision),
        currency character varying(3) NOT NULL CHECK (currency::text <> ''::text),
        picture_urls text[] NOT NULL,
        user_id bigint NOT NULL REFERENCES "user"(user_id),
        public boolean NOT NULL,
        featured boolean NOT NULL,
        created_at timestamp with time zone NOT NULL,
        start timestamp with time zone,
        included bytea,
        with_who integer NOT NULL DEFAULT 0,
        kind integer NOT NULL DEFAULT 0,
        language integer NOT NULL DEFAULT 1,
        CONSTRAINT trip_check CHECK (min_participants >= 0 AND min_participants <= max_participants),
        CONSTRAINT trip_check1 CHECK (max_participants >= 0 AND max_participants >= min_participants)
    );
    COMMENT ON COLUMN trip.with_who IS '0:未選択, 1:カップル・夫婦, 2:家族, 3:一人, 4:友達';
    COMMENT ON COLUMN trip.kind IS '0:未選択, 1:デート, 2:プロポーズ, 3:新婚旅行';
    COMMENT ON COLUMN trip.language IS '1:日本語   2:日本語以外';
    
    -- Indices -------------------------------------------------------
    
    CREATE UNIQUE INDEX trip_pkey ON trip(trip_id int8_ops);
    
[/code]

GORMでデバッグを行った際に、表示されるSQL文を直接コマンドで実行しても同じエラーが出るので、PostgreSQLの問題ではないかと思います。

[code]

     SELECT question.*, question.id AS question_id, trip.*, answer.trip_id, user.*  FROM "question" left outer join answer on answer.question_id = question.id join trip on trip.trip_id = answer.trip_id join user on user.user_id = answer.user_id WHERE (question.id = 3)
    
[/code]

エラーメッセージの意味がわからず困っているので、お助けください。

バージョン情報  
Go : 1.14.1  
PostgreSQL : 12.2


CC BY-SA 4.0
2020-06-10T05:25:31.257
67489
2020-06-11T00:38:39.160
2020-06-10T06:05:35.790
3068
40563
question
go postgresql gorm
PostgreSQLで特定のテーブルJOINのみエラーが出る


userがPostgreSQLの予約語だったため、以下のようにバッククオーテーションを使って、userテーブル名がダブルクオーテーションで囲われるように改良しました。

[code]

    if err := config.DB.Table("question").Select(`question.*, question.created_at AS question_created_at, trip.*, answer.trip_id AS answer_trip_id,
            "qu".name AS question_user_name, "qu".picture_url AS question_user_image,
            "au".name AS answer_user_name, "au".picture_url AS answer_user_image
        `).
        Joins("left join answer on answer.question_id = question.id").
        Joins("left join trip on trip.trip_id = answer.trip_id").
        Joins(`left join "user" AS au ON "au"."user_id" = answer.user_id`).
        Joins(`left join "user" AS qu ON question.user_id = "qu"."user_id"`).
        Where("question.id = ?", id).Scan(&result).Error; err != nil {
        c.JSON(http.StatusBadRequest, err)
    } else {
        c.JSON(http.StatusOK, result)
    }
    
[/code]


CC BY-SA 4.0
2020-06-11T00:38:39.160
67520
2020-06-11T00:38:39.160
40563
67489
answer