
Vertica can display numbers in scientific notation in decimal form with ease. That is, unless the value is a string!
Example:
dbadmin=> SELECT '2.01803E+13'::NUMERIC::INT "This now works!";
This now works!
-----------------
20180300000000
(1 row)
dbadmin=> SELECT '2.01803E+13'::INT "This does not work!";
ERROR 3681: Invalid input syntax for integer: "2.01803E+13"
You first have to convert the VARCHAR to a NUMERIC and then to an INT.
Like this:
dbadmin=> SELECT '2.01803E+13'::NUMERIC::INT "This now works!";
This now works!
-----------------
20180300000000
(1 row)
The same is true when trying to load a value stored in a file in Scientific Notation into a integer table column.
dbadmin=> CREATE TABLE s (c INT);
CREATE TABLE
dbadmin=> \! cat /home/dbadmin/s.txt
2.01803E+13
dbadmin=> COPY s FROM '/home/dbadmin/s.txt' REJECTED DATA TABLE s_bad;
Rows Loaded
-------------
0
(1 row)
dbadmin=> SELECT rejected_reason FROM s_bad;
rejected_reason
-------------------------------------------------------
Invalid integer format '2.01803E+13' for column 1 (c)
(1 row)
To successfully load this data, we can use the FILLER parameter and load it as a NUMERIC and then convert it to an INT!
dbadmin=> COPY s (c_filler FILLER NUMERIC, c AS c_filler::INT) FROM '/home/dbadmin/s.txt' REJECTED DATA TABLE s_bad;
Rows Loaded
-------------
1
(1 row)
dbadmin=> SELECT * FROM s;
c
----------------
20180300000000
(1 row)
You can also do this:
dbadmin=> COPY s (c_filler FILLER VARCHAR, c AS c_filler::NUMERIC::INT) FROM '/home/dbadmin/s.txt' REJECTED DATA TABLE s_bad;
Rows Loaded
-------------
1
(1 row)
dbadmin=> SELECT * FROM s;
c
----------------
20180300000000
20180300000000
(2 rows)
Helpful Links:
https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/BulkLoadCOPY/IgnoringColumnsAndFieldsInTheLoadFile.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/DataTypes/DataTypeCoercion.htm
Have fun!