Loading Data Stored in Scientific Notation to an Integer Column

Jim_Knicely · July 2019

Scientific notation is a way of expressing numbers that are too big or too small to be conveniently written in decimal form.

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::INT "This works!";
  This 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 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/9.2.x/HTML/Content/Authoring/AdministratorsGuide/BulkLoadCOPY/IgnoringColumnsAndFieldsInTheLoadFile.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/DataTypes/DataTypeCoercion.htm

Have fun!