I use Postgres with the Postgis extension and pgx & sqlf on the Go side.
Inserting/deleting and reading Rows works fine I'm just struggeling with Updates of a row since I always get this error returned from pgx
when I try to scan the returned row into a Go struct.
no rows in result set
Below is the method I created to do partial updates. I first creates the SQL query and set columns to update and also add a RETURNING
statement to return the whole row. Since my location column is a Postgis Point I need to wrap the column with the ST_AsBinary
function so that I can scan it into an Orb.Point.
type UpdatePartyParams struct {
ID string
Title string
Lat float32
Long float32
StreetAddress string
PostalCode string
State string
Country string
StartDate time.Time
EndDate time.Time
}
func (d Directory) UpdateParty(ctx context.Context, arg UpdatePartyParams) (Party, error) {
sqlf.SetDialect(sqlf.PostgreSQL)
b := sqlf.Update(TABLE_NAME)
if arg.Title != "" {
b = b.Set("title", arg.Title)
}
if arg.Lat != 0 && arg.Long != 0 {
b = b.SetExpr("location", "ST_SetSRID(ST_MakePoint(?, ?), 4326)", arg.Long, arg.Lat)
}
if arg.StreetAddress != "" {
b = b.Set("street_address", arg.StreetAddress)
}
if arg.PostalCode != "" {
b = b.Set("postal_code", arg.PostalCode)
}
if arg.State != "" {
b = b.Set("state", arg.State)
}
if arg.Country != "" {
b = b.Set("country", arg.Country)
}
startYear := arg.StartDate.Year()
if !(startYear == 1970) {
b = b.Set("start_date", arg.StartDate)
}
endYear := arg.StartDate.Year()
if !(endYear == 1970) {
b = b.Set("end_date", arg.EndDate)
}
b.
Where("id = ?", arg.ID).
Returning("id, user_id, title, is_public, ST_AsBinary(location) AS location, street_address, postal_code, state, country, start_date, end_date")
log.Println(b.String()) // UPDATE parties SET title=$1 WHERE id = $2 RETURNING id, user_id, title, is_public, ST_AsBinary(location) AS location, street_address, postal_code, state, country, start_date, end_date
row := d.pool.QueryRow(ctx, b.String(), b.Args()...)
var i Party
err := row.Scan(
&i.ID,
&i.UserID,
&i.Title,
&i.IsPublic,
wkb.Scanner(&i.Location),
&i.StreetAddress,
&i.PostalCode,
&i.State,
&i.Country,
&i.StartDate,
&i.EndDate,
)
return i, err
}
My Database Schema
CREATE TABLE parties (
id varchar(27) PRIMARY KEY,
user_id TEXT NOT NULL,
title TEXT NOT NULL,
is_public BOOLEAN NOT NULL DEFAULT false,
location geometry(POINT, 4326) NOT NULL,
street_address TEXT,
postal_code TEXT,
state TEXT,
country TEXT,
start_date TIMESTAMP,
end_date TIMESTAMP
);
The mentioned error is caused by the row.Scan method and thrown "If no rows were found". But when I execute the sql statement with a difference SQL UI client it works fine and the row is returned. Any Idea what could cause the row.Scan()
method to fail and the row not to be returned?
Edit
The weird part is that a simple read for one row works fine and scans the returned record into the Go struct. Below is a working method which uses the same row.Scan
method just through a select
statement.
func (d PartyRepository) GetParty(ctx context.Context, id string) (Party, error) {
sqlf.SetDialect(sqlf.PostgreSQL)
b := sqlf.
Select("id, user_id, title, is_public, ST_AsBinary(location) AS location, street_address, postal_code, state, country, start_date, end_date").
From(TABLE_NAME).
Where("id = ?", id)
log.Println(b.String())
row := d.pool.QueryRow(ctx, b.String(), b.Args()...)
var i Party
err := row.Scan(
&i.ID,
&i.UserID,
&i.Title,
&i.IsPublic,
wkb.Scanner(&i.Location),
&i.StreetAddress,
&i.PostalCode,
&i.State,
&i.Country,
&i.StartDate,
&i.EndDate,
)
return i, err
}