Insert into table with postgis geometry type

[Napam]

Hello, go-jet has been great so far, but I have hit a problem that I struggle to find a good solution for.

So, basically I have a table with a column that is defined as: coordinates geometry (POINT, 4326). The geometry is from a the Postgis extension.

Now, I want to do a database insert. Since go-jet doesn't recognize the geometry type, it generates the column as string, which is fine, as I make an "override" using the "Wrap generated types" method explained in the docs: https://github.com/go-jet/jet/wiki/FAQ#wrap-generated-types

I define the following type:

// Postgis go jet wrapper
type Geometry struct {
	Lon float64
	Lat float64
}

func (g *Geometry) Scan(value interface{}) error {
	return fmt.Errorf("Don't read geometry directly, but do SELECT ST_X(geometry_column), ST_Y(geometry_column) instead")
}

func (g Geometry) Value() (driver.Value, error) {
	return fmt.Sprintf("ST_SetSRID(ST_MakePoint(%f, %f), 4326)", g.Lon, g.Lat), nil
}

And I use it as so:

mendedListing := MendedListing{
    Listings: listing,
    Coordinates: &Geometry{
        Lon: 1.2345,
        Lat: 6.7890,
    },
}

insertStatement := t.Listings.INSERT(
    t.Listings.AllColumns,
).MODEL(
    mendedListing,
)

Now this won't work. After digging through the database/sql code I see that the program basically sends the following query to postgres:

INSERT INTO listings (..., coordinates, ...)
VALUES (..., $61, ...);

with the "nvdargs" as

[... { 61 ST_SetSRID(ST_MakePoint(1.2345, 6.7890), 4326)} ...]

the nvdargs thing I am talking about is an array of the following struct:

// NamedValue holds both the value name and value.
type NamedValue struct {
	// If the Name is not empty it should be used for the parameter identifier and
	// not the ordinal position.
	//
	// Name will not have a symbol prefix.
	Name string

	// Ordinal position of the parameter starting from one and is always set.
	Ordinal int

	// Value is the parameter value.
	Value Value
}

Point being; postgres won't understand the ST_SetSRID(ST_MakePoint(1.000000, 1.000000), 4326) as a named value:

postgres-1  | 2024-09-26 18:11:05.736 UTC [35] ERROR:  parse error - invalid geometry
postgres-1  | 2024-09-26 18:11:05.736 UTC [35] HINT:  "ST" <-- parse error at position 2 within geometry
postgres-1  | 2024-09-26 18:11:05.736 UTC [35] CONTEXT:  unnamed portal parameter $61 = '...'

Hypothetical solution

From my understanding, what should have been done is the following:

The insert statement sent to postgres should actually have been:

INSERT INTO listings (..., coordinates, ...)
VALUES (..., ST_SetSRID(ST_MakePoint($61, $62), 4326), ...);

then the namedvalue array could have been

[... { 61 1.2345} {62 6.7890} ...]

A way to solve this is to use a raw query, which I would really like to avoid as this table is kinda huge. I am wondering if there is a better way?

Maybe I could do something about the model generation itself? Should I make a custom column type? Is that possible?

[houten11]

You are trying to send part of the sql query as an argument, which is not possible without some form of eval function.

Jet way would be to define two missing functions first:

func ST_MakePoint(lon, lat float64) Expression {
	return Func("ST_MakePoint", Float(lon), Float(lat))
}

func ST_SetSRID(point Expression, srid int64) Expression {
	return Func("ST_SetSRID", point, Int(srid))
}

Which then can be used like any query expression:

ST_SetSRID(ST_MakePoint(11.22, 33.44), 4326).EQ(Table.Point)

Also, note that now you'll have to use VALUES instead MODEL method for insert:

insertStatement := Listings.INSERT(
    Listings.AllColumns,
).VALUES(
    ...,
    ST_SetSRID(ST_MakePoint(1.2345, 6.7890), 4326),
    ...,
)

[Napam]

Ah yes! This worked, and is much nicer than a raw sql query.

Thank you very much!