Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Marshalling of JSON data silently fails when column includes an underscore #2274

Open
Aerek-Yasa opened this issue Mar 11, 2025 · 1 comment
Labels

Comments

@Aerek-Yasa
Copy link

Aerek-Yasa commented Mar 11, 2025

Describe the bug
When the name of a database column includes and underscore character, pgx.CollectRows with pgx.RowToStructByName fails to to correctly marshall the data into a struct.

To Reproduce
The following code will produce the bug. The values stored in column_one and column_three will fail to be carried over into the struct, but the values stored in columntwo and columnfour will correctly carry over.
This example is also available in the github repository https://github.com/Aerek-Yasa/pgxtest .
The test requires the test runner to fill in the credentials and location for a test database at the top of the file. The test database is expected to already be created.

package main

import (
	"context"
	"fmt"

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

var databaseUser string = ""
var databasePassword string = ""
var databaseHost string = ""
var databasePort int32 = 0
var databaseName string = ""

type PGXTestTable struct {
	ColumnOne   int32   `db:"column_one"`
	ColumnTwo   int32   `db:"columntwo"`
	ColumnThree []int32 `db:"column_three"`
	ColumnFour  []int32 `db:"columnfour"`
}
type Wrapper struct {
	Values []PGXTestTable `db:"values"`
}

func main() {
	if databaseUser == "" {
		fmt.Println("Please add the username for the test database into the main.go file")
		return
	}
	if databasePassword == "" {
		fmt.Println("Please add the password for the test database into the main.go file")
		return
	}
	if databaseHost == "" {
		fmt.Println("Please add the hostname for the test database into the main.go file")
		return
	}
	if databasePort == 0 {
		fmt.Println("Please add the port for the test database into the main.go file")
		return
	}
	if databaseName == "" {
		fmt.Println("Please add the database name for the test database into the main.go file")
		return
	}
	configString := fmt.Sprintf("user=%s password=%s host=%s port=%d dbname=%s", databaseUser, databasePassword, databaseHost, databasePort, databaseName)
	config, err := pgxpool.ParseConfig(configString)
	if err != nil {
		fmt.Println(err)
		return
	}
	pool, err := pgxpool.NewWithConfig(context.Background(), config)
	if err != nil {
		fmt.Println(err)
		return
	}
	_, err = pool.Exec(context.Background(), `
		CREATE TABLE IF NOT EXISTS pgx_test_table (
			column_one 	 INT,
			columntwo	 INT,
			column_three INT[],
			columnfour   INT[]
		);
	`)
	if err != nil {
		fmt.Println(err)
		return
	}
	_, err = pool.Exec(context.Background(), `
		INSERT INTO pgx_test_table
		(column_one, columntwo, column_three, columnfour)
		VALUES
		(1001, 1011, '{1110}', '{1100}'),
		(2002, 2022, '{2220}', '{2200}');
	`)
	if err != nil {
		fmt.Println(err)
		return
	}
	rows, err := pool.Query(context.Background(), `
		SELECT 
			COALESCE(
				(SELECT 
					json_agg(row_to_json(pgx_test_table))
					FROM pgx_test_table
				),
				'[]'::json
			) values
	`)
	if err != nil {
		fmt.Println(err)
		return
	}
	defer rows.Close()
	wrapper, err := pgx.CollectRows[Wrapper](rows, pgx.RowToStructByName[Wrapper])
	if err != nil {
		fmt.Println(err)
		return
	}
	for _, thing := range wrapper[0].Values {
		fmt.Println("Got values: ", thing.ColumnOne, thing.ColumnTwo, thing.ColumnThree, thing.ColumnFour)
	}
}

Expected behavior
The program is expected to output the values :

  • 1001, 1011, [1110] and [1100] for the first database row
  • 2002, 2022, [2220] and [2200] for the second database row

Actual behavior
The program outputs the following values;

  • 0, 1011, [ ] and [1100] for the first database row
  • 0, 2022, [ ], and [2200] for the second database row

Version

  • Go: go version go1.22.4 windows/amd64
  • PostgreSQL: PostgreSQL 15.2, compiled by Visual C++ build 1914, 64-bit'`
  • pgx: v5.6.0 and v5.7.2 tested
@Aerek-Yasa Aerek-Yasa added the bug label Mar 11, 2025
@jackc
Copy link
Owner

jackc commented Mar 15, 2025

pgx doesn't do any json parsing on its own. It uses the encoding/json package. If some of your fields aren't being populated I suspect you need json tags.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

No branches or pull requests

2 participants