Division (/) Not Giving My Answer in Postgresql

Division ( / ) not giving my answer in postgresql

Your columns have integer types, and integer division truncates the result towards zero. To get an accurate result, you'll need to cast at least one of the values to float or decimal:

select cast(dev_cost as decimal) / sell_cost from software ;

or just:

select dev_cost::decimal / sell_cost from software ;

You can then round the result up to the nearest integer using the ceil() function:

select ceil(dev_cost::decimal / sell_cost) from software ;

(See demo on SQLFiddle.)

Division in postgres

Assuming all papers have at least one author:

select count(*) * 1.0 / count(distinct paper_id)
from authors;

The * 1.0 is to avoid integer division.

Division of integers returns 0

You should cast before you divide, but also you were missing a subquery to get the total count from the table. Here's the sample.

select 
  random_int,
  count(random_int) as Count,
  cast(count(random_int) as decimal(7,2)) / cast((select count(random_int) from test) as decimal(7,2)) as Percent
from test
group by random_int
order by random_int;

postgresql division by zero error handling

I think that you need to add some NULLIF validation to the denominator too and add a COALESCE clause.

WITH query1 AS (
   SELECT well_id, produced_at, oil,
  (EXTRACT(EPOCH FROM age(produced_at,
                          LAG(produced_at) OVER w))/3600)::int as hourly_rate
                          FROM public.production
WINDOW w AS (PARTITION BY well_id ORDER BY well_id, produced_at 
             ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
     
),
query2 as (
   select Well_id, produced_at, nullif(oil,0), hourly_rate,
   COALESCE((nullif(oil,0)/nullif(hourly_rate*6,0)),0) as Last_Six_Hours_Of_Production,
   COALESCE((nullif(oil,0)/nullif(hourly_rate*12,0)),0) as Last_Twelve_Hours_Of_Production
    from query1
)
Select *
from query2;

Incorrect attempt: Relational Division in PostgreSQL using Not Exist/Not In

You can use EXISTS twice:

select f.*
from Film f 
where 
  exists (
    select 1 from Film_Actor 
    where film_id = f.film_id and actor_id = 105
  )
  and
  exists (
    select 1 from Film_Actor 
    where film_id = f.film_id and actor_id = 122
  )
order by f.title

or by grouping first the Film_Actor table and then join to the Film table:

select f.* from Film f inner join ( 
  select film_id 
  from Film_Actor
  where actor_id in (105, 122)
  group by film_id
  having count(actor_id) = 2
) g on g.film_id = f.film_id
order by f.title

postgresql round division

Actually, when I try your code in Postgres 9.3.4 using pgAdmin, both return the values you want. The values are not in exponential notation. Hence, I suspect this is an issue with your application, not the database.

An easy way to check is to put the value as a string:

select round( 1/100000000::decimal, 8 )::text

This should not return exponential notation.

Performing division with PostgreSQL / json

To extract a scalar value (not another json) use the ->> operator (not ->).

That returns type text. So you also need to cast before doing math on the number:

(manifest ->> 'size')::bigint  -- or numeric? see below!

See:

• Postgres data type cast

You could do integer division, which truncates, computing 598 like you projected:

SELECT 628032520 / 1024 / 1024  -- 598  -- truncated!

Casting to a floating point number or numeric avoids integer division and shows your sample value to be much closer to 599:

SELECT 628032520.0 / 2^20  -- 598.9384841918945313  -- precise

The numeric constant 123 resolves to type integer, while 123.0 (containing a point .) resolves to numeric.

And 1024 * 1024 = 1048576 = 2^20.

Maybe round? Or keep two fractional digits?

SELECT round(628032520.0 / 2^20)     -- 599    -- rounded
     , round(628032520.0 / 2^20, 2)  -- 599.94

But consider the built-in function pg_size_pretty() instead which is made for this very purpose:

SELECT pg_size_pretty(628032520.0)  -- 599 MB  -- formatted text
     , pg_size_pretty(232520.0)     -- 227 kB

So:

SELECT c.name AS customer, i.customer_id, i.captured_at, i.name AS image
     , pg_size_pretty((c.manifest ->> 'size')::numeric) AS image_size
FROM   public.images i
JOIN   public.customers c ON c.id = i.customer_id
WHERE  captured_at > date_trunc('day', now()) - interval '12 months'
ORDER  BY captured_at DESC;

Should give you:

customer   customer_id   captured_at   image        image_size
Acme Inc   12345         2022-05-31    Central HMA  599 MB  

I also fixed a couple other issues with your query.

Assuming captured_at is type timestamptz (like it probably should be), current_date would introduce a dependency on time zone setting of the session, which is a sneaky, unnecessary source of corner-case errors. See:

• PostgreSQL date() with timezone

And "name" is not a good name.

---

Related Topics