Encode Plan for Nested Arrays of Custom Type

[carmichaeljr]

I have a struct like the following:

type PhysicsData struct {
   Time [][]types.Second
   // ... many other fields
}

types.Second is defined in a separate package:

type Second float64

If I have the following query defined in a file that sqlc parses:

-- name: CreatePhysicsData :one
INSERT INTO providentia.physics_data(time, ... many other fields)
VALUES ($1, ... many other fields) RETURNING id;

When I run the above sql query with the struct shown below I get no error.

PhysicsData{
    Time: [][]types.Second{[]types.Second{1,2,3}},
}

However, If I try to run the above sql query with any of the following structs I get the error shown below.

PhysicsData{
    Time: [][]types.Second{[]types.Second{1,2,3}, nil},
}
PhysicsData{
    Time: [][]types.Second{[]types.Second{1,2,3}, []types.Second{}},
}
PhysicsData{
    Time: [][]types.Second{[]types.Second{1,2,3}, []types.Second{0}},
}

failed to encode args[1]: unable to encode [][]types.Second{[]types.Second{0, ...}, ...} into binary format for _float8 (OID 1022): cannot find encode plan

I have found several discussions about registering custom types but (1) why is that not necessary when inserting the first struct and (2) if I do really need to register types how can I register a type from a separate package?

[jackc]

It's hard to say without being able to see the actual queries. If you can reduce it to a self-contained example that might help.

[carmichaeljr]

CREATE TABLE IF NOT EXISTS physics_data (
	id SERIAL8 NOT NULL PRIMARY KEY,
	time FLOAT8[][] NOT NULL,
);

// Seconds defined in types package
type Second float64

package main

import (
	"context"
	"fmt"
	"os"

	"tmp/types"
	"github.com/jackc/pgx/v5/pgxpool"
)

type PhysicsData struct {
	Time [][]types.Second
}

func main() {
	var testDBPool *pgxpool.Pool

	var err error
	var poolConf *pgxpool.Config
	if poolConf, err = pgxpool.ParseConfig(fmt.Sprintf(
		"host=%s port=%d user=%s password=%s dbname=%s",
		"localhost", 5432, "postgres", "postgres", "tests",
	)); err != nil {
		panic(err)
	}
	if testDBPool, err = pgxpool.NewWithConfig(
		context.Background(), poolConf,
	); err != nil {
		panic(err)
	}
	if err = testDBPool.Ping(context.Background()); err != nil {
		panic(err)
	}

	sql := `INSERT INTO physics_data(time) VALUES ($1);`
	s := PhysicsData{
		Time: [][]types.Second{[]types.Second{1, 2, 3}},                       // No error
		// Time: [][]types.Second{[]types.Second{1, 2, 3}, nil},               // Error
		// Time: [][]types.Second{[]types.Second{1, 2, 3}, []types.Second{}},  // Error
		// Time: [][]types.Second{[]types.Second{1, 2, 3}, []types.Second{0}}, // Error
	}
	row := testDBPool.QueryRow(context.Background(), sql, s.Time)

	var id int64
	err = row.Scan(&id)
	if err != nil {
		panic(err)
	}
	fmt.Println(id)

	return
}

[jackc]

There are multiple issues here.

First, I was able to reduce the scope of your example significantly. See below for the code. In particular, custom types have nothing to do with the issue you are experiencing. The same behavior occurs when using float64 directly.

Second, once example was reduced, the problem became clearer. You are trying to do things that PostgreSQL doesn't support. See what happens if you try to do this in psql.

postgres@[local]:5015 pgx_test=# select '{{1,2,3}}'::float8[];
  float8
───────────
 {{1,2,3}}
(1 row)

Time: 1.164 ms
postgres@[local]:5015 pgx_test=# select '{{1,2,3},null}'::float8[];
ERROR:  22P02: malformed array literal: "{{1,2,3},null}"
LINE 1: select '{{1,2,3},null}'::float8[];
               ^
DETAIL:  Unexpected array element.
LOCATION:  ArrayCount, arrayfuncs.c:660
Time: 0.763 ms
postgres@[local]:5015 pgx_test=# select '{{1,2,3},{}}'::float8[];
ERROR:  22P02: malformed array literal: "{{1,2,3},{}}"
LINE 1: select '{{1,2,3},{}}'::float8[];
               ^
DETAIL:  Unexpected "}" character.
LOCATION:  ArrayCount, arrayfuncs.c:589
Time: 0.648 ms
postgres@[local]:5015 pgx_test=# select '{{1,2,3},{1}}'::float8[];
ERROR:  22P02: malformed array literal: "{{1,2,3},{1}}"
LINE 1: select '{{1,2,3},{1}}'::float8[];
               ^
DETAIL:  Multidimensional arrays must have sub-arrays with matching dimensions.
LOCATION:  ArrayCount, arrayfuncs.c:605
Time: 0.665 ms

Reduced scope example:

package main

import (
	"context"
	"fmt"
	"os"

	"github.com/jackc/pgx/v5/pgxpool"
)

func main() {
	testDBPool, err := pgxpool.New(context.Background(), os.Getenv("DATABASE_URL"))
	if err != nil {
		panic(err)
	}
	if err = testDBPool.Ping(context.Background()); err != nil {
		panic(err)
	}

	sql := `INSERT INTO physics_data(time) VALUES ($1) returning id;`
	t := [][]float64{{1, 2, 3}} // No error
	// t := [][]float64{{1, 2, 3}, nil} // Error
	// t := [][]float64{{1, 2, 3}, {}}  // Error
	// t := [][]float64{{1, 2, 3}, {0}} // Error
	var id int64
	err = testDBPool.QueryRow(context.Background(), sql, t).Scan(&id)
	if err != nil {
		panic(err)
	}
	fmt.Println(id)
}

[carmichaeljr]

I see, I let the error message deter me from trying a simpler example without custom types. Thank you for taking the time to answer my question.