CODEWITHSUNDEEP
postgresqlgogo-gorm
sofs1
sofs1

Reputation: 4176

Why does the gorm postgresql throws pq: syntax error at or near ")"?

SELECT_QUERY = `SELECT * FROM events WHERE c_id = ? AND start_time > ? and
                                    end_time < ?`

query := sr.db.Raw(SELECT_QUERY, request.GetCId(), startTime, endTime)

    var v = request.GetVIds()
    if len(v) > 0 {
        query = query.Where(` v_id IN (?) `, v)
    } //Only this block introduces first ) after end_time

var c = request.GetStatus().String()
    if len(c) > 0 {
        query = query.Where( " status = ? ", c) // this introduces the other opening brace //after AND
    }

Following is the query generated and found in logs

 SELECT * FROM events WHERE c_id = 1 AND start_time > '2020-04-16 18:42:00' and
                                        end_time < '2020-04-16 18:45:50' ) AND ( v_id IN (1,2)) AND ( status = 'STATUS_MIDDLE_CLASS'  ORDER BY  start_time DESC  LIMIT 5 OFFSET 1

The other solution in stackoverflow and internet article doesn't help.

PS: Is it because I mix db.Raw( ) and query.Where() ?

Upvotes: 0

Views: 5252

Answers (2)

James Martin
James Martin

Reputation: 124

I found a workaround to the error which I received when I tried to add a timestamp filed in Go/Gorm solution with PostgreSQL equivalent to default: now() which is default: time.Now().Format(time.RFC3339)

I received the error because I use AutoMigrate() to create the tables in PostgreSQL. The one problem I found is when trying to use the default value of a function instead of a string (which one can use for a fixed timestamp).

So I had to go into DataGrid (since I use JetBrains, but you can use any PostgreSQL admin tool like pgAdmin) and manually add the timestamp field with default of now() or merely update the existing field to add the default of now(). Then the error goes away when doing your next build in Go.

Upvotes: 0

sofs1
sofs1

Reputation: 4176

Changing ? to $1 doesn't fix the issue.

Basically a few things fixed the issue.

1) Mixing Raw and query.Where was one issue.

After making the Raw query to sr.db.Where

2) 

SELECT_QUERY = `SELECT * FROM events WHERE c_id = ? AND start_time > ? and
                                    end_time < ?`

already has select * from. And then using query := sr.db.Raw(SELECT_QUERY, request.GetCId(), startTime, endTime) introduces nested select *.

So, changed SELECT_QUERY as follows 

SELECT_QUERY = `events WHERE c_id = ? AND start_time > ? and
                                    end_time < ?`

solved the issue.

Upvotes: 1

Related Questions