Go struct generation for WITH coalesced JSONb not working as expected for pgx/v5 - interface{} instead of []byte

[dennisharrison]

Version

1.26.0

What happened?

Been playing with this since yesterday - I have a sample query that is modified to remove columns and change table names.

Starting to dig into this in earnest since I can't figure out any magic override incantation to get what I'm looking for - which is overrides CTE result to be a []byte instead of interface{}

Looking here: https://github.com/sqlc-dev/sqlc/blob/main/internal/codegen/golang/postgresql_type.go#L186

Then here: https://github.com/sqlc-dev/sqlc/blob/main/internal/codegen/golang/opts/enum.go#L28

Leading me to also examine: https://github.com/sqlc-dev/sqlc/blob/main/internal/codegen/golang/driver.go#L9

I haven't run it in a debugger yet to step through and see what it thinks the case is for this: https://github.com/sqlc-dev/sqlc/blob/main/internal/codegen/golang/postgresql_type.go#L186

But I'm reasonably sure that driver at this point is opts.SQLDriverPGXV5 enum.

Here is the config:

version: "2"
sql:
- schema: "schema.sql"
  queries: "queries.sql"
  engine: "postgresql"
  gen:
    go:
      package: "dao"
      sql_package: "pgx/v5"
      sql_driver: "github.com/jackc/pgx/v5"
      out: "../repository/dao"

Query:

WITH
    amount_values AS (
        SELECT
            cro.tag_type,
            cro.override_tag_number,
            json_agg(
                    jsonb_build_object(
                            'valueType',
                            croav.value_type,
                            'values',
                            ARRAY[croav.rule_override_value]
                    )
            )::jsonb AS amount_values
        FROM
            puppy_overrides cro
                LEFT JOIN puppy_overrides_amount_values croav ON cro.tag_type = croav.tag_type
                AND cro.override_tag_number = croav.override_tag_number
        GROUP BY
            cro.tag_type,
            cro.override_tag_number
    )
SELECT
    puppy.tag_type,
    puppy.tag_number,
    puppy.category_code,
    puppy.compute_type,
    puppy.last_updated,
    COALESCE(
                    json_agg(
                    jsonb_build_object(
                            'id',
                            cro.override_tag_number,
                            'name',
                            cro.rule_override_name,
                            'description',
                            cro.description,
                            'computeTypeDetails',
                            jsonb_build_object(
                                    'chargeAmount',
                                    cro.compute_type_details ->> 'chargeAmount',
                                    'amountApplyToValues',
                                    COALESCE(av.amount_values, '[]'::jsonb)
                            ),
                            'accountingCode',
                            cro.accounting_code
                    )
                            ) FILTER (
                        WHERE
                        cro.override_tag_number IS NOT NULL
                        )::jsonb,
                    '[]'::jsonb
    ) AS overrides
FROM
    puppies puppy
        LEFT JOIN puppy_overrides cro ON puppy.tag_type = cro.tag_type
        AND puppy.tag_number = cro.overridden_tag_number
        LEFT JOIN amount_values av ON cro.tag_type = av.tag_type
        AND cro.override_tag_number = av.override_tag_number
WHERE
    puppy.tag_type = @tag_type
  AND puppy.tag_number = @tag_number
GROUP BY
    puppy.tag_type,
    puppy.tag_number,
    puppy.category_code,
    puppy.last_updated;

Generated Struct:

type SelectPuppyRow struct {
	TagType               string
	TagId                 int32
	CategoryCode           string
	ComputeType            string
	LastUpdated            []byte
	Overrides              interface{}
}

Relevant log output

No response

Database schema

No response

SQL queries

No response

Configuration

No response

Playground URL

No response

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[dennisharrison]

I was able to get around this by casting the JSONb into a BYTEA - still curious as to why I need to do that in this case and if it can be avoided in the future.

[DustinJSilk]

im trying to figure out how to have a field set to []byte, or even a string, rather than an interface{}. do you have an example that you managed to work? casting the type doesn't seem to solve it for me

EDIT

Figured it out, had the cast in the wrong place:

COALESCE(json_agg(json_build_object(...)), '[]')::bytea AS name