Trying to find median

userACSl1 0
Viewed 36

While executing the query to find median I stumble upon this error message- BIGINT UNSIGNED value is out of range in '(median.row_asc - median.row_desc)'

Table

The query that I wrote:

with median as
(select lat_n, row_number () over (order by lat_n asc) as row_asc,row_number () over (order by lat_n desc) as row_desc
from station
 
 )
 select round(avg(lat_n),4)
 from median
 where abs(row_asc-row_desc) <= 1

While doing the same kind in postgresql by creating the table

create table station (id serial primary key,
                      city varchar(50),
                     state varchar(50),
                     lat_n float,
                      long_w float)
                      
insert into station values 
(2,'ctc','odisha',31.546,33.213),
(3,'khorda','odisha',30.546,39.213),                  
(4,'puri','odisha',41.546,37.213);  

with median as
(select lat_n,row_number() over (order by lat_n) as row_asc,
row_number() over(order by lat_n desc) as row_desc from station

)

select round(avg(lat_n),4)
from median
where abs(row_asc-row_desc) <=1

the error:

ERROR:  function round(double precision, integer) does not exist
LINE 7: select round(avg(lat_n),4)
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
sql postgresql
1 Answers

The problem is that the function requires the first argument to be of type numeric:

\df round
                         List of functions
   Schema   │ Name  │ Result data type │ Argument data types │ Type 
════════════╪═══════╪══════════════════╪═════════════════════╪══════
 pg_catalog │ round │ double precision │ double precision    │ func
 pg_catalog │ round │ numeric          │ numeric             │ func
 pg_catalog │ round │ numeric          │ numeric, integer    │ func

and there is no implicit cast from double precision to numeric:

                         List of casts
   Source type    │ Target type │ Function │   Implicit?   
══════════════════╪═════════════╪══════════╪═══════════════
 double precision │ numeric     │ numeric  │ in assignment

According to the documentation, only implicit casts are considered:

Function Type Resolution

[...]

  1. Look for the best match.

       a. Discard candidate functions for which the input types do not match and cannot be converted (using an implicit conversion) to match.

So you will have to add an explicit cast:

round(CAST (avg(lat_n) AS numeric), 4)
Laurenz Albe0
Related