Fetch Records That Are Non Zero After the Decimal Point in Postgresql

Fetch records that are non zero after the decimal point in PostgreSQL

numeric is exact!

Unlike claimed by another answer, numeric is not a floating-point type, but an arbitrary precision type as defined by the SQL standard. Storage is exact. I quote the manual:

The type numeric can store numbers with a very large number of digits
and perform calculations exactly. It is especially recommended for
storing monetary amounts and other quantities where exactness is required.

Answer

The natural candidate for your question is the function trunc(). It truncates toward zero - basically keeping the integer part while discarding the rest. Fastest in a quick test, but the difference is insubstantial among the top contenders.

SELECT * FROM t WHERE amount <> trunc(amount);

floor() truncates to the next lower integer, which makes a difference with negative numbers:

SELECT * FROM t WHERE amount <> floor(amount);

If your numbers fit into integer / bigint you can also just cast:

SELECT * FROM t WHERE amount <> amount::bigint;

This rounds to full numbers, unlike the above.

Test

Tested with PostgreSQL 9.1.7. Temporary table with 10k numeric numbers with two fractional digits, around 1% have .00.

CREATE TEMP TABLE t(amount) AS
SELECT round((random() * generate_series (1,10000))::numeric, 2);

Correct result in my case: 9890 rows. Best time from 10 runs with EXPLAIN ANALYZE.

Erwin 1

SELECT count(*) FROM t WHERE amount <> trunc(amount)          -- 43.129 ms

mvp 2 / qqx

SELECT count(*) FROM t WHERE amount != round(amount)          -- 43.406 ms

Erwin 3

SELECT count(*) FROM t WHERE amount <> amount::int            -- 43.668 ms

mvp 1

SELECT count(*) FROM t WHERE round(amount,2) != round(amount) -- 44.144 ms

Erwin 4

SELECT count(*) FROM t WHERE amount <> amount::bigint         -- 44.149 ms

Erwin 2

SELECT count(*) FROM t WHERE amount <> floor(amount)          -- 44.918 ms

Nandakumar V

SELECT count(*) FROM t WHERE amount - floor(amount) > .00     -- 46.640 ms

Mostly still true in Postgres 12 (except everything's > 10x faster now). Test with 100k rows instead of 10k:

db<>fiddle here

postgresql store decimal value with trailing zero using type real

Real values are stored in binary format, so you can only decide in what form they will be shown. You can cast real values to numeric using (precision, scale) like this:

with my_table(the_num) as (
values
    (6.0::real),
    (6.1),
    (6.15)
)

select the_num, the_num::numeric(15,1), the_num::numeric(15,2)
from my_table

 the_num | the_num | the_num 
---------+---------+---------
       6 |     6.0 |    6.00
     6.1 |     6.1 |    6.10
    6.15 |     6.2 |    6.15
(3 rows)

Alternatively, you can use the function to_char(), e.g.

select the_num, to_char(the_num, '999D9'), to_char(the_num, '999D99')
from my_table

 the_num | to_char | to_char 
---------+---------+---------
       6 |    6.0  |    6.00
     6.1 |    6.1  |    6.10
    6.15 |    6.2  |    6.15
(3 rows)    

You can also use the numeric type instead of real in the table definition, e.g.:

create table my_table (
    the_num numeric(15, 2)
);

In this case the values will be stored with the defined scale.

Read more about Numeric Types in the documentation.

SQL - How do I get only the numbers after the decimal?

one way, works also for negative values

declare @1 decimal(4,3)
select @1 = 2.938

select PARSENAME(@1,1)

PostgreSQL adds trailing zeros to numeric

I think this is it, if I am understanding "coerce" correctly in this context. This is from the PostgreSQL docs:

Both the maximum precision and the maximum scale of a numeric column
can be configured. To declare a column of type numeric use the syntax:

NUMERIC(precision, scale)

The precision must be positive, the scale zero or positive.
Alternatively:

NUMERIC(precision)

selects a scale of 0. Specifying:

NUMERIC

without any precision or scale creates a column in
which numeric values of any precision and scale can be stored, up to
the implementation limit on precision. A column of this kind will not
coerce input values to any particular scale, whereas numeric
columns with a declared scale will coerce input values to that scale.

Bold emphasis mine.

So it is misleading later in the same section:

Numeric values are physically stored without any extra leading or
trailing zeroes. Thus, the declared precision and scale of a column
are maximums, not fixed allocations.

Bold emphasis mine again.

This may be true of the precision part, but since the scale is being coerced when it is defined, trailing zeros are being added to the input values to meet the scale definition (and I would assume truncated if too large).

I am using precision,scale definitions for constraint enforcement. It is during the DB insert that the trailing zeros are being added to the numeric scale, which seems to support the coercion and conflicts with the statement of no trailing zeros being added.

Correct or not, I had to handle the problem in code after the select is made. Lucky for me the impacted attributes are BigDecimal so stripping trailing zeros was easy (albeit not graceful). If someone out there has a better suggestion for not having PostgreSQL add trailing zeros to the numeric scale on insert, I am open to them.

How to round an average to 2 decimal places in PostgreSQL?

PostgreSQL does not define round(double precision, integer). For reasons @Mike Sherrill 'Cat Recall' explains in the comments, the version of round that takes a precision is only available for numeric.

regress=> SELECT round( float8 '3.1415927', 2 );
ERROR:  function round(double precision, integer) does not exist

regress=> \df *round*
                           List of functions
   Schema   |  Name  | Result data type | Argument data types |  Type  
------------+--------+------------------+---------------------+--------
 pg_catalog | dround | double precision | double precision    | normal
 pg_catalog | round  | double precision | double precision    | normal
 pg_catalog | round  | numeric          | numeric             | normal
 pg_catalog | round  | numeric          | numeric, integer    | normal
(4 rows)

regress=> SELECT round( CAST(float8 '3.1415927' as numeric), 2);
 round 
-------
  3.14
(1 row)

(In the above, note that float8 is just a shorthand alias for double precision. You can see that PostgreSQL is expanding it in the output).

You must cast the value to be rounded to numeric to use the two-argument form of round. Just append ::numeric for the shorthand cast, like round(val::numeric,2).

---

If you're formatting for display to the user, don't use round. Use to_char (see: data type formatting functions in the manual), which lets you specify a format and gives you a text result that isn't affected by whatever weirdness your client language might do with numeric values. For example:

regress=> SELECT to_char(float8 '3.1415927', 'FM999999999.00');
    to_char    
---------------
 3.14
(1 row)

to_char will round numbers for you as part of formatting. The FM prefix tells to_char that you don't want any padding with leading spaces.

Select numbers with more than 4 decimal places

DECLARE @tbl TABLE (val float)
INSERT INTO @tbl SELECT 1234.567
INSERT INTO @tbl SELECT 1234.5678
INSERT INTO @tbl SELECT -1234.5678
INSERT INTO @tbl SELECT 1234.56789

SELECT *
from @tbl
where (((val*10000) - CONVERT(INT,(val*10000))) <> 0)

Counting spaces before and after a decimal point

SELECT LEN(ColumnA )
      ,CHARINDEX('.',REVERSE(ColumnA ))-1
FROM Table1

If a value has no decimal, the above will return -1 for the spaces after decimal, so you could use:

SELECT LEN(ColumnA)
      ,CASE WHEN ColumnA LIKE '%.%' THEN CHARINDEX('.',REVERSE(ColumnA))-1
            ELSE 0
       END
FROM Table1

Demo of both: SQL Fiddle

If you just wanted the MAX() then you'd just wrap the above in MAX():

SELECT MAX(LEN(ColumnA ))
      ,MAX(CHARINDEX('.',REVERSE(ColumnA ))-1)
FROM Table1

---

Related Topics