Correct db_type for overriding integers is confusing

[kyleconroy]

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

[maxiride]

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.

[henrybaxter]

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!

[fpieper]

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"

[renanferr]

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

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

[cmarkh]

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!

[cmarkh]

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