Only certain table JOIN errors in PostgreSQL

Asked 1 years ago, Updated 1 years ago, 110 views

Only certain table JOINs fail in PostgreSQL

We are developing API servers in Go language and PostgreSQL.

At that time, the following error occurs only when the user table is used as JOIN.

Starting from the question table, I am writing a SELECT statement that joins answer, trip, and user.

{
    "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"
}

The source code is written in GORM as follows:

 func GetQuestionDetail(c*gin.Context){
    id,err: = strconv.Atoi(c.Param("id"))
    if err!=nil{
        c. JSON (http.StatusBadRequest, err)
    }
    typeResult structure {
        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
    iferr:=config.DB.Table("question").Select("question.*, question.id AS question_id, trip.*, answer.trip_id, user.*").
        Joins("leftouter 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)
    }
}

Table Structure

user

 --Table Definition ------------------------------------------------------------------------------------

US>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,
    family 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);

question

 --Table Definition ------------------------------------------------------------------------------------

US>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);

answer

 --Table Definition ------------------------------------------------------------------------------------

US>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);

trip

 --Table Definition ------------------------------------------------------------------------------------

US>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),
    current character variation (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 DEFAULT1,
    CONSTRAINT trip_check CHECK (min_participants>=0ANDmin_participants<=max_participants),
    CONSTRAINT trip_check1CHECK (max_participants>=0 AND max_participants>=min_participants)
);
COMMENT ON COLUMN trip.with_who IS'0:unselected, 1:couple/husband, 2:family, 3:one, 4:friend';
COMMENT ON COLUMN trip.kind IS '0: Not Selected, 1: Date, 2: Proposal, 3: honeymoon';
COMMENT ON COLUMN trip.language IS '1: Japanese 2: Non-Japanese';

-- Indices ----------------------------------------------------------------------

CREATE UNIQUE INDEX trip_pkey ON trip(trip_id int8_ops);

When I debug in GORM, I get the same error when I run the SQL statement directly with the command, so I think it's a PostgreSQL problem.

SELECT question.*, question.id AS question_id, trip.*, answer.trip_id, user.* FROM "left outer join answer on answer.question_id=question.id join tryp on tryp.trip.id=answer.trip_id.user.=on

I'm having trouble understanding the meaning of the error message, so please help me.

About
Go: 1.14.1
PostgreSQL: 12.2

go postgresql gorm

2022-09-30 14:50

1 Answers

Since user was a reserved word for PostgreSQL, we have improved the user table name to include double-quotes using the back-quotation as follows:

 iferr: = 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`ASau ON`au`.`user_id`=answer.user_id`).
    Joins(`left join"user"ASquON 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)
}


2022-09-30 14:50

If you have any answers or tips


© 2024 OneMinuteCode. All rights reserved.