Correct db_type for overriding integers is confusing

Given the following schema:

CREATE TABLE foo (
    bar INTEGER NOT NULL
);

It would seem that the correct way to override the type for the bar column would be this:

overrides:
  - go_type: "example.com/some.Type"
    db_type: "integer"

Due to the SQL parser we use for Postgres, the column type is automatically converted into a canonical format. In this case, it translates integer to pg_catalog.int4. The correct configuration file is this:

overrides:
  - go_type: "example.com/some.Type"
    db_type: "pg_catalog.int4"

This is very confusing. We should probably attempt to canonicalize the db_type value the same way as the parser.

First reported in #412

I've encountered this issue too with the db_type VARCHAR.

Given the schema

CREATE TABLE foo (
    bar VARCHAR(50)
);

the correct override configuration is:

overrides:
  - go_type: "example.com/some.Type"
    db_type: "varchar"

On the subject of others string types, the Postgres type TEXT is correctly overridden with db_type: "text"

The initial issue alongside with the varchar one makes me think that other types too might not be recognized correctly without the pg_catalogue representation. Maybe an endtoend test should be added to check that all Postgres types are correctly recognized? Anyway I suppose it's a relatively low priority issue.

I would add that overriding decimal is also confusing, for example in my particular case I had:

CREATE TABLE foo (
    bar decimal NOT NULL
);

and the correct override for me was:

overrides:
  - db_type: "pg_catalog.numeric"
    go_type: "github.com/shopspring/decimal"

Posting for anyone else who may come across it - thanks for the awesome project!

Maybe something has changed, but I needed to specify the exact type Decimal

overrides:
  - db_type: "pg_catalog.numeric"
    go_type: "github.com/shopspring/decimal.Decimal"

For the record, to override smallint type I had to do:

overrides:
    - db_type: "pg_catalog.int2"
      go_type: "uint8"

Do you know if there is a way to override a numeric type that can be null?

The following works for me but then it doesn't handle the null case.

overrides:
        - db_type: pg_catalog.numeric
          go_type: github.com/shopspring/decimal.Decimal
          nullable: true

If I remove the nullable: true or set it to false then it reverts back to pgtype.Numeric.

Fwiw, it would also be very helpful to get warnings when an overridden db_type can't be overridden with the provided go_type. Though maybe a numeric than can be null is not considered the same as a numeric not null.

Thanks for the amazing package!

Apologies, it seems I spoke too soon. I just discovered the github.com/shopspring/decimal package has a NullDecimal type that parses perfectly for this purpose. I'll leave my comment in case anyone finds it helpful.

overrides:
        - db_type: pg_catalog.numeric
          go_type: github.com/shopspring/decimal.NullDecimal
          nullable: true

Navigation Menu

Repository navigation

Correct db_type for overriding integers is confusing #421

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Correct db_type for overriding integers is confusing #421

Description

Activity

maxiride commented on May 6, 2020

henrybaxter commented on Dec 13, 2020

fpieper commented on Aug 9, 2022

renanferr commented on Dec 13, 2023

cmarkh commented on Jan 2, 2024

cmarkh commented on Jan 2, 2024

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions