Hi ,
We have a scenario where we need to store long decimal values in Vertica tables (more than 13 decimals) eg: 10.123456789123456789.
What exactly the data type we need to use in Vertica to store this value.
We have tried with Decimal and we can store the value, but while retrieving we will be able to see only 13 characters length.
Another alternative is to store the value in VARCHAR format.....!!! But wanted to check is there any other way in Vertica.
Thank You!!
How to store Decimal value with more than 13 digits
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: How to store Decimal value with more than 13 digits
Hi,
The numeric and decimal types (they are the same) should work.
Example:
Note: NUMERICS that have a precision of 18 or less have similar performance characteristics as INTEGER, regardless of the scale. When possible Vertica recommends using a precision <= 18.
Take a look here for more info.:
http://my.vertica.com/docs/7.2.x/HTML/i ... UMERIC.htm
The numeric and decimal types (they are the same) should work.
Example:
Code: Select all
dbadmin=> create table test (c1 numeric(20, 18), c2 decimal(20, 18));
CREATE TABLE
dbadmin=> \d test;
List of Fields by Tables
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+----------------+------+---------+----------+-------------+-------------
public | test | c1 | numeric(20,18) | 16 | | f | f |
public | test | c2 | numeric(20,18) | 16 | | f | f |
(2 rows)
dbadmin=> insert into test values(10.123456789123456789, 10.123456789123456789);
OUTPUT
--------
1
(1 row)
dbadmin=> select * from test;
c1 | c2
-----------------------+-----------------------
10.123456789123456789 | 10.123456789123456789
(1 row)
Take a look here for more info.:
http://my.vertica.com/docs/7.2.x/HTML/i ... UMERIC.htm
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.