jackc / jackc/pgx

Multidimensional array flattened when using CopyFrom instead of Exec+Insert

Open
#2,385 7 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

bug
Dominant language
Go
Stars
14.3k
Forks
1.1k
Avg merge
6d 9h
Merged PRs (30d)
11

Description

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.

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start by running the provided Go reproduction against PostgreSQL and compare Exec insertion with CopyFrom. Inspect the CopyFrom handling of PostgreSQL array values, then add a regression test showing that multidimensional text arrays retain their dimensions and verify the existing CopyFrom behavior remains intact.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, postgresql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
55/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.