Add support for POSTGIS geometry type

[rupertevill]

The following SQL:

CREATE TABLE test_table (
    geometry_column GEOMETRY
);

yields the following error when using 'sqlc generate':

2020/01/07 11:33:35 unknown Postgres type: geometry
2020/01/07 11:33:35 unknown Postgres type: geometry
2020/01/07 11:33:35 unknown Postgres type: geometry
2020/01/07 11:33:35 unknown Postgres type: geometry
2020/01/07 11:33:35 unknown Postgres type: geometry
2020/01/07 11:33:35 unknown Postgres type: geometry
2020/01/07 11:33:35 unknown Postgres type: geometry
2020/01/07 11:33:35 unknown Postgres type: geometry
2020/01/07 11:33:35 unknown Postgres type: geometry
2020/01/07 11:33:35 unknown Postgres type: geometry

It would be fantastic if geometry could default to simply use the sql.NullString for now.

If it is added, it allows me to reference the geometry type from https://github.com/paulmach/go.geojson as the type override (at the package level). Rather than having to reference each geometry column separately if I want to override the type.

[clansofts]

@rupertevill, I have fixed this particular error by doing the following.
in your sqlc.json add following override

"overrides": [
{
"go_type": "github.com/twpayne/go-geom.MultiPolygon",
"db_type": "geography",
"null": true
},
{
"go_type": "github.com/twpayne/go-geom.MultiPolygon",
"db_type": "geometry",
"null": true
}]

am using https://github.com/twpayne/go-geom

In my case am using both geometry and geography . with default of MultiPolygons

Its generating below field

type Boundary struct {
ID int64 json:"id"
Uid uuid.UUID json:"uid"
BlockNo sql.NullString json:"block_no"
ParcelNo sql.NullString json:"parcel_no"
Geom geom.MultiPolygon json:"geom"
}

[tschaub]

I'm curious how others are dealing with the need to call ST_AsBinary or ST_AsEWKB when selecting or ST_GeomFromEWKB etc on insert. I've reported the trouble I've been having in #384.

[chaitan94]

The following worked out for me, without needing ST_AsBinary:

overrides:
  - db_type: "geometry"
    go_type: "github.com/cridenour/go-postgis.Point"

Note: For geometries having an SRID, use github.com/cridenour/go-postgis.PointS instead,

The schema I used for testing this:

CREATE TABLE city (
    id serial primary key,
    center geometry not null
);

-- name: GetCity :one
SELECT
  id,
  center
FROM city
WHERE id = $1;

(sqlc playground link for this: https://play.sqlc.dev/p/bf6cdb6d5efccd911da525068d29561d822845ceee9ffc152ff8fa18b633da72)

[egtann]

For completeness, here's how I was able to insert using postgis.PointS on a geometry(Point, 4326) column:

-- name: CreateGeo :exec
INSERT INTO geo_data (location) VALUES (ST_GeomFromEWKB(@location));

Then using it:

err := db.CreateGeo(ctx, postgis.PointS{
    SRID: 4326,
    X: 1,
    Y: 2,
})

[vladimir-budjen]

For completeness, here's how I was able to insert using postgis.PointS on a geometry(Point, 4326) column:

-- name: CreateGeo :exec
INSERT INTO geo_data (location) VALUES (ST_GeomFromEWKB(@location));

Then using it:

err := db.CreateGeo(ctx, postgis.PointS{
    SRID: 4326,
    X: 1,
    Y: 2,
})

You saved me