sqlc-dev
failed to encode args[2]: unable to encode &geom.Point{geom0:geom.geom0{layout:1, stride:2, flatCoords:[]float64{38.8951, 77.0364}, srid:0}} into text format for unknown type (OID 17977): cannot find encode plan
Version
1.26.0
What happened?
I am trying to simply store geometry data using postgis.
The insert currently does not work. I have tried following https://docs.sqlc.dev/en/stable/reference/datatypes.html#using-github-com-twpayne-go-geom but currently failing to insert into my DB.
Relevant log output
failed to encode args[2]: unable to encode &geom.Point{geom0:geom.geom0{layout:1, stride:2, flatCoords:[]float64{38.8951, 77.0364}, srid:0}} into text format for unknown type (OID 17977): cannot find encode plan
Database schema
BEGIN;
CREATE EXTENSION IF NOT EXISTS "postgis";
-- Create tables
CREATE TABLE "observation" (
"id" BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
"uid" UUID DEFAULT uuid_generate_v4(),
"user_id" BIGINT NOT NULL,
"location" GEOMETRY(POINT, 4326) NOT NULL,
"created_at" TIMESTAMPTZ NOT NULL DEFAULT (CURRENT_TIMESTAMP),
"facility_name" VARCHAR(255) NOT NULL
);
-- Add foreign keys
ALTER TABLE "observation" ADD CONSTRAINT "observation_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "user" ("id");
-- Add indexes
CREATE UNIQUE INDEX "observation_uid_idx" ON "observation" USING BTREE ("uid");
CREATE INDEX "observation_user_id_idx" ON "observation" USING BTREE ("user_id");
COMMIT;
// Code generated by sqlc. DO NOT EDIT.
// versions:
// sqlc v1.26.0
// source: observation.sql
package pgsqlc
import (
"context"
go_geom "github.com/twpayne/go-geom"
)
const createObservation = `-- name: CreateObservation :exec
INSERT INTO "observation" (
"user_id",
"facility_name",
"location"
)
VALUES (
$1,
$2,
$3
)
`
type CreateObservationParams struct {
UserID int64
FacilityName string
Location *go_geom.Point
}
func (q *Queries) CreateObservation(ctx context.Context, arg CreateObservationParams) error {
_, err := q.db.Exec(ctx, createObservation, arg.UserID, arg.FacilityName, arg.Location)
return err
}
const getObservationByID = `-- name: GetObservationByID :one
SELECT id, uid, user_id, location, created_at, facility_name FROM "observation"
WHERE "id" = $1 LIMIT 1
`
func (q *Queries) GetObservationByID(ctx context.Context, id int64) (Observation, error) {
row := q.db.QueryRow(ctx, getObservationByID, id)
var i Observation
err := row.Scan(
&i.ID,
&i.UID,
&i.UserID,
&i.Location,
&i.CreatedAt,
&i.FacilityName,
)
return i, err
}
const getObservationsByUserID = `-- name: GetObservationsByUserID :many
SELECT id, uid, user_id, location, created_at, facility_name FROM "observation"
WHERE "user_id" = $1
`
func (q *Queries) GetObservationsByUserID(ctx context.Context, userID int64) ([]Observation, error) {
rows, err := q.db.Query(ctx, getObservationsByUserID, userID)
if err != nil {
return nil, err
}
defer rows.Close()
items := []Observation{}
for rows.Next() {
var i Observation
if err := rows.Scan(
&i.ID,
&i.UID,
&i.UserID,
&i.Location,
&i.CreatedAt,
&i.FacilityName,
); err != nil {
return nil, err
}
items = append(items, i)
}
if err := rows.Err(); err != nil {
return nil, err
}
return items, nil
}
SQL queries
-- name: CreateObservation :exec
INSERT INTO "observation" (
"user_id",
"facility_name",
"location"
)
VALUES (
$1,
$2,
$3
);
-- name: GetObservationByID :one
SELECT * FROM "observation"
WHERE "id" = $1 LIMIT 1;
-- name: GetObservationsByUserID :many
SELECT * FROM "observation"
WHERE "user_id" = $1;
Configuration
version: "2"
sql:
- schema: "db/postgres/migration"
queries: "db/postgres/query"
engine: "postgresql"
gen:
go:
package: "pgsqlc"
out: "internal/infrastructure/persistence/pgsqlc"
sql_package: "pgx/v5"
emit_json_tags: false
emit_interface: true
emit_empty_slices: true
rename:
uid: "UID"
client_ip: "ClientIP"
overrides:
- db_type: "timestamptz"
go_type: "time.Time"
- db_type: "timestamptz"
go_type: "time.Time"
nullable: true
- db_type: "uuid"
go_type: "github.com/google/uuid.UUID"
- db_type: "uuid"
go_type: "github.com/google/uuid.UUID"
nullable: true
- db_type: "geometry"
go_type:
import: "github.com/twpayne/go-geom"
pointer: true
type: "Point"
- db_type: "geometry"
go_type:
import: "github.com/twpayne/go-geom"
pointer: true
type: "Point"
nullable: true
Playground URL
No response
What operating system are you using?
macOS
What database engines are you using?
PostgreSQL
What type of code are you generating?
Go
To make this simpler, I have followed this playground: https://play.sqlc.dev/p/f19156612c3d4875ee51893bd8204c484be6818a0162cb9f40f02f80a43fc157
So I am overriding like so:
- db_type: "geometry"
- go_type: "github.com/cridenour/go-postgis.Point"
The table is created like so:
CREATE TABLE "observation" (
"id" BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
"uid" UUID DEFAULT uuid_generate_v4(),
"user_id" BIGINT NOT NULL,
"location" geometry(Point, 4326) NOT NULL,
"created_at" TIMESTAMPTZ NOT NULL DEFAULT (CURRENT_TIMESTAMP),
"facility_name" VARCHAR(255) NOT NULL
);
Then, my generated model looks like so:
type Observation struct {
ID int64 `json:"id"`
UID uuid.UUID `json:"uid"`
UserID int64 `json:"user_id"`
Location postgis.Point `json:"location"`
CreatedAt time.Time `json:"created_at"`
FacilityName string `json:"facility_name"`
}
The INSERT SQL is:
-- name: CreateObservation :one
INSERT INTO observation (
user_id,
facility_name,
location
)
VALUES (
$1,
$2,
$3
)
RETURNING *;
And I attempt to insert like so:
func (u *ObservationManagementService) CreateObservation(ctx context.Context, newObservation ports.NewObservation) (pgsqlc.Observation, error) {
args := pgsqlc.CreateObservationParams{
UserID: newObservation.UserID,
FacilityName: newObservation.FacilityName,
Location: postgis.Point{
X: newObservation.Long,
Y: newObservation.Lat,
},
}
o, err := u.store.CreateObservation(ctx, args)
return o, err
}
I am getting this error when trying to insert:
ERROR: parse error - invalid geometry (SQLSTATE XX000)
Not sure it is directly relevant but this issue popped up for me when I ran into a similar issue. The problem I had was that PGX had no idea about my custom type when I was using a postgres array.
To solve this, I added an AfterConnect function to my config which told PGX about my custom type. An example of how to set this up is on the PGX issue board - https://github.com/jackc/pgx/issues/1601#issuecomment-1591237100
I am not sure if this should be fixed in SQLC. Maybe you are already doing this, but not adding the array version, I haven't dug deep into your code.
