PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres unable to detect correct type of integer field as inspected using ResultSetMetaData

I build an Excel document from a result set returned from PostgresQL database.
I need to being able to distinguish between decimal (double) and non-decimal (integer) numbers returned by the result set.

My SQL looks like followging:

...
 (select product, reportflag,
                        (select measure_name from report_measures rm where rm.measure_key = typeflag and rm.object_id=(select object_id from reportdet)) as salesLoss,
                        (case when skunumber = 'MULTIPLE' then '-1' else skunumber end)::varchar(12) as skunumber, skuname, null::varchar(12) as substitutesku,
                        null as substituteskuname,
                        null::decimal as substituteTransferSales,
                        case when measure_symbol[3] = '%' then ((measures ->> measure_key[3])::decimal)/100 else (measures ->> measure_key[3])::decimal end as totalSpend, 
                        case when measure_symbol[4] = '%' then ((measures ->> measure_key[4])::decimal)/100 else (measures ->> measure_key[4])::bigint end as totalUnits,  <-- this neeeds to be an integer
                        case when measure_symbol[5] = '%' then ((measures ->> measure_key[5])::decimal)/100 else (measures ->> measure_key[5])::decimal end as totalVisits,
...

I need totalUnits field to be an integer of some sort. However, it’s returned as a decimal.

I use the following to detect the field’s type:

public void processRow(ResultSet rs) throws SQLException {
    var rsmd = rs.getMetaData();
    ...
}

Then, as I inspect rcmd, I can see the following types:

rsmd = {PgResultSetMetaData@21873} 
 connection = {PgConnection@21880} 
 fields = {Field[55]@21881} 
  0 = {Field@21882} "Field(product,TEXT,65535,T)"
  1 = {Field@21883} "Field(reportflag,TEXT,65535,T)"
  2 = {Field@21884} "Field(salesloss,VARCHAR,65535,T)"
  3 = {Field@21885} "Field(skunumber,VARCHAR,65535,T)"
  4 = {Field@21886} "Field(skuname,TEXT,65535,T)"
  5 = {Field@21887} "Field(substitutesku,VARCHAR,65535,T)"
  6 = {Field@21888} "Field(substituteskuname,TEXT,65535,T)"
  7 = {Field@21889} "Field(substitutetransfersales,NUMERIC,65535,T)"
  8 = {Field@21890} "Field(totalspend,NUMERIC,65535,T)"
  9 = {Field@21891} "Field(totalunits,NUMERIC,65535,T)" <-- this should be an integer
  10 = {Field@21892} "Field(totalvisits,NUMERIC,65535,T)"
  ...

As we can see, totalunits comes back as NUMERIC, which is a decimal type, despite me casting that to bigint.

I’ve tried to use explicit cast of that field to a bigint in SQL as below:

    case when measure_symbol[4] = '%' then ((measures ->> measure_key[4])::decimal)/100
 else CAST(measures ->> measure_key[4] AS bigint) end as totalUnits,

, with exactly the same result as above.

At the same time, the skunumber filed above is correctly cast as varchar.

What did I get wrong?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    As @Albina correctly pointed out, both sides of the case statement should return the same type.

    While digging, I came across this error: "CASE types character varying and numeric cannot be matched", which led me to the following answer, which confirmed @Albina's solution: ERROR: CASE types character varying and numeric cannot be matched

    Additionally, I had to round to 0 since the results were coming with a trailing .0. So the final answer I ended up with is:

    ROUND(case when measure_symbol[4] = '%' then ((measures ->> measure_key[4])::decimal)/100 else (measures ->> measure_key[4])::decimal end, 0)::bigint as totalUnits,

  2. 0

    I suppose the issue in CASE-statement.
    The statement may return different data types (decimal & bigint, and decimal is a common type for both), so there is no type consistency.

    From your query:

    case when measure_symbol[4] = '%' 
     then ((measures ->> measure_key[4])::decimal)/100 <-- it returns DECIMAL
     else (measures ->> measure_key[4])::bigint end as totalUnits,  <-- this neeeds to be an INTEGER

    As a solution, you can cast the whole CASE-statement:

    CAST(
    case 
        when measure_symbol[4] = '%' then ((measures ->> measure_key[4])::decimal)/100
        else measures ->> measure_key[4]::bigint 
    end
AS bigint) as totalUnits,
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search