1

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
}
JonasLevin
  • 1,592
  • 1
  • 20
  • 50

0 Answers0