How to insert multi-dimensional arrays using pgx/v5

[pacifico3000]

Hello!

In my code I have two-dimensional slice of type [][]int32 which i cannot insert into postgres table.

The table is like this:
CREATE TABLE mytable ( id BIGINT PRIMARY KEY, fill int[][], ifilled_at TIMESTAMP NOT NULL );

Doing something like:
fill := [][]int32{[]int32{1, 1440}, []int32{2, 1250}} pool.Exec( ctx, postgres.FormatQuery(update mytable set fill = $1, filled_at = $2 where id = $3), fill, filledAt, id)

and an error looks like unable to encode [][]int32{...} into text format for unknown type (OID 0): cannot find encode plan

It is working if I use lib/pq: pq.Array(fill), but maybe there's some easy way to use pgx/v5/pgtype Array type that I don't see?

[jackc]

Works for me.

package main

import (
	"context"
	"log"
	"os"

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

func main() {
	ctx := context.Background()

	conn, err := pgx.Connect(ctx, os.Getenv("DATABASE_URL"))
	if err != nil {
		log.Fatal(err)
	}
	defer conn.Close(ctx)

	_, err = conn.Exec(ctx, "create temporary table t (id bigint primary key, ary int[][]);")
	if err != nil {
		log.Fatal(err)
	}

	_, err = conn.Exec(ctx, "insert into t values (1, $1);", [][]int{{1, 2}, {3, 4}})
	if err != nil {
		log.Fatal(err)
	}

	var ary [][]int
	err = conn.QueryRow(ctx, "select ary from t where id = 1;").Scan(&ary)
	if err != nil {
		log.Fatal(err)
	}

	log.Printf("ary: %v", ary)
}

jack@glados ~/dev/pgx_issues/pgx-2263 ±master⚡ » go run .
2025/02/25 06:57:13 ary: [[1 2] [3 4]]

[pacifico3000]

Well, looks like the problem is somewhere on my side.

Thank you for a quick answer! :)

[codercms]

I believe the problem is still happens when you try to encode an array that looks like [][]int{{1, 2}, nil}

[jackc]

@codercms That is invalid value in PostgreSQL. Multidimensional arrays must have sub-arrays with matching dimensions.