squirrel
squirrel copied to clipboard
Postgres BIGINT value not being created or updated
I posted an issue here : https://stackoverflow.com/questions/65858504/postgres-bigint-value-not-being-created-or-updated-with-golang-and-sqlx
Here is the content.
I am doing this :
Note : Request is generated with squirrel
// Prepare query
qb := sq.Update(d.table).
SetMap(updates).
Where(filter).
PlaceholderFormat(sq.Dollar)
// Build sql query
q, args, err := qb.ToSql()
if err != nil {
return fmt.Errorf("postgresql: unable to build query: %w", err)
}
fmt.Println(q, args)
// Prepare the statement
stmt, err := d.session.PreparexContext(ctx, q)
if err != nil {
return fmt.Errorf("postgresql: unable to prepare query: %w", err)
}
// Do the insert query
res, err := stmt.ExecContext(ctx, args...)
if err != nil {
return fmt.Errorf("postgresql: unable to execute query: %w", err)
}
With this data :
// Updates
updates := map[string]interface{}{
"acknowledgment_duration": obj.AcknowledgmentDuration,
}
type sqlIssue struct {
...
AcknowledgmentDuration int `db:"acknowledgment_duration"`
I am sure that the value is not 0 when I print it I got the right value, the SQL request is good when I print it, but whatever I try the value in Postgress stays at "0". It is not being updated, nor created with the right value.
The generated SQL request is :
UPDATE issues SET ..................., acknowledgment_duration = $9, .......... WHERE id = $14 [ ......... 10000000000 ..... ]
SQL table is :
-- +migrate Up
CREATE TABLE issues (
...
acknowledgment_duration BIGINT,
Am I missing something ?
If I perform the request myself into the database with a too such as Adminer, it is working.. So it must be inside my Golang code I guess...
Do you see the issue please ? I can't figure it out...........
If the output of ToSql
is correct then it isn't an issue with squirrel. Those values get passed on to whatever database driver you are using.
Yeah... crazy.. Any idea on where I could investigate ? oO
I am using postgres driver
Double check the query by passing the builder to https://godoc.org/github.com/Masterminds/squirrel#DebugSqlizer and printing the result.
Good advice ! :)
I have this error : [DebugSqlizer error: not enough placeholders in ..........
, but the update is working.. except that field that cause me an issue. How can the update be working and the debugger saying that there is an error .. ? oO
There isn't enough context in your posts to answer. Try giving the full query and/or link to source
This is the full request : UPDATE issues SET description = '', title = 'xxx', workflow_created_at = '2021-01-19 07:50:13 +0100 CET', workflow_created_by = 'xxxx', workflow_c_analyzed_at = '0001-01-01 00:00:00 +0000 UTC', workflow_c_analyzed_by = '', workflow_l1_acknowledgment_at = '2021-01-19 07:58:37 +0100 CET', workflow_l1_acknowledgment_by = 'dgarcia', workflow_l1_acknowledgment_duration = '504000000000', workflow_l1_analyzed_at = '2021-01-19 08:37:45 +0100 CET', workflow_l1_analyzed_by = 'xxx', workflow_l2_analyzed_at = '2021-01-19 11:33:47 +0100 CET', workflow_l2_analyzed_by = 'xxx' WHERE id = 'OFS5kxFmqD1wOjVS
The error was due to the placeholder bug of the debugger : https://github.com/Masterminds/squirrel/issues/116
I merged a fix for that bug. Could you post the output of ToSql instead?
Here it is : UPDATE issues SET description = $1, title = $2, workflow_created_at = $3, workflow_created_by = $4, workflow_c_analyzed_at = $5, workflow_c_analyzed_by = $6, workflow_l1_acknowledgment_at = $7, workflow_l1_acknowledgment_by = $8, workflow_l1_acknowledgment_duration = $9, workflow_l1_analyzed_at = $10, workflow_l1_analyzed_by = $11, workflow_l2_analyzed_at = $12, workflow_l2_analyzed_by = $13 WHERE id = $14 [ xxxxx 2021-01-19 12:35:12 +0100 CET xxxxx 0001-01-01 00:00:00 +0000 UTC 2021-01-19 12:36:27 +0100 CET xxxx 75000000000 2021-01-19 12:50:09 +0100 CET xxxx 2021-01-20 16:19:15 +0100 CET xxxx OlTYf02rZc0ycF1m]
The value of the BIGINT is 75000000000, all the data is correct, the SQL request seems to be good, I can't see it....