Multidimensional array flattened when using CopyFrom instead of Exec+Insert

[rrb3942]

Describe the bug When using CopyFrom to insert a string value of a postgres formatted multidimensional array, the array is getting flattened. If inserted via Exec the dimensions are properly persevered.

My specific scenario is bulk loading from a csv with fields containing postgres formatted arrays into a text[][] column.

To Reproduce Steps to reproduce the behavior:

In postgres:

 create table array_test ( array_col text[][] );

package main

import (
        "context"
        "fmt"
        "os"

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

func main() {
        conn, err := pgx.Connect(context.Background(), os.Getenv("DATABASE_URL"))

        if err != nil {
                fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
                os.Exit(1)
        }

        defer conn.Close(context.Background())

        multi := "{{one,two,three},{four,five,six}}"
        rows := [][]any{
                        {multi},
                }

        //will insert as {{one,two,three},{four,five,six}}
        _, err = conn.Exec(context.Background(), "insert into array_test (array_col) values ($1)", multi)

        if err != nil {
                fmt.Println(err)
                os.Exit(1)
        }

        //will insert as {one,two,three,four,five,six}
        _, err = conn.CopyFrom(context.Background(), pgx.Identifier{"array_test"}, []string{"array_col"}, pgx.CopyFromRows(rows))

        if err != nil {
                fmt.Println(err)
                os.Exit(1)
        }

        fmt.Println("insert ok")
}

Expected behavior Array dimensions to be preserved and identical behavior to Exec+Insert.

Actual behavior Array dimensions flattened.

Version

• Go: create table array_test ( array_col text[][] );
• PostgreSQL: PostgreSQL 17.6 (Debian 17.6-1.pgdg12+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14+deb12u1) 12.2.0, 64-bit
• pgx: v5.7.6

Additional context psql with COPY or \COPY from the csv works as expected.

[jackc]

I have determined what the problem is, but a full solution is eluding me.

Here's what is happening. pgx's CopyFrom always uses the binary format. However, you are passing it a string. Normally, that will work. pgx determines the type that PostgreSQL needs and parses the text into that type, then encodes that in binary into the copy stream. It's a bit inefficient, but it normally works.

Unfortunately, the PostgreSQL type system does not distinguish between the dimensions of arrays. That is, PostgreSQL has a text[] type, but doesn't actually have a text[][] type. In fact, an array column can have values of varying dimensions.

Typically, when parsing a value, pgx is scanning the value into a value provided to it. e.g. a []string. However, in this path it doesn't have a value provided so it has to choose what to scan into before it looks at the value. In ArrayCodec.DecodeValue it ultimately chooses []any as the value to scan into. And pgx when scanning a multi-dimensional array into a Go slice, pgx will flatten the array rather than fail.

Potentially, the behavior of ArrayCodec.DecodeValue could be changed, but that might have unintended side effects on other uses.

---

However, for your use case in particular, there may be a much simpler solution. If you go down a layer to PgConn.CopyFrom you can directly stream your CSV directly to PostgreSQL. That should avoid the problem and have better performance as well.

[rrb3942]

I was able to do an implementation using PgConn.CopyFrom directly, thanks for the advice!

Yeah, I noticed postgresql was a little loose with array dimensions. When I created a multidim array for a column type, \d on the table only showed a single dimension. I guessed this might have been related, but wasn't sure.

Feel free to close this issue unless you want it left open to track investigating ArrayCodec.DecodeValue changes.

[jackc]

I'll leave it open for now. It is a bug. Even if one I'm not sure can be fixed.