DecimalDelim option
Specifies the decimal delimiter for the following data types: float, double, numeric, time, timetz, and timestamp. This option supports both text-delimited and fixed-length formats. The default is '.'.
Examples
Following are examples of how to use the decimal delimiter option.
The option use is shown in
bold text.
- The following example is for the text-delimited
format for the table
level:
INSERT INTO <target-table> SELECT * FROM '<external-table>' USING (delim 'delim' DecimalDelim ','); - The following example is for the fixed-length
format for the table
level:
INSERT INTO <target-table> SELECT * FROM '<external-table>' USING (DecimalDelim ',' format 'fixed' layout (c1 int bytes 4, c2 float bytes 6, c3 numeric(10,2) bytes 11, c4 time 24HOUR ':' bytes 11 ); - The following examples are for the fixed-length format for the column level:
- The following example is for the numeric data type:
INSERT INTO <target-table> SELECT * FROM '<external-table>' USING (format 'fixed' layout (c1 int bytes 4 , c2 float bytes 6, c3 numeric(10,2) decimal ',' bytes 11) ); INSERT INTO <target-table> SELECT * FROM '<external-table>' USING (format 'fixed' layout (c1 int bytes 4 , c2 float bytes 6, c3 numeric(10,2) decimal DecimalDelim ',' bytes 11) ); - The following example is for the float data
type:
INSERT INTO <target-table> SELECT * FROM '<external-table>' USING (format 'fixed' layout (c1 int bytes 4 , c2 float floating ',' bytes 6, c3 numeric(10,2) bytes 11) ); INSERT INTO <target-table> SELECT * FROM '<external-table>' USING (format 'fixed' layout (c1 int bytes 4 , c2 float floating DecimalDelim ',' bytes 6, c3 numeric(10,2) bytes 11) ); - The following example is for the double data type:
INSERT INTO <target-table> SELECT * FROM '<external-table>' USING (format 'fixed' layout (c1 int bytes 4 , c2 double exponential ',' bytes 6, c3 numeric(10,2) bytes 11) ); INSERT INTO <target-table> SELECT * FROM '<external-table>' USING (format 'fixed' layout (c1 int bytes 4 , c2 float exponential DecimalDelim ',' bytes 6, c3 numeric(10,2) bytes 11) ); - The following examples are for the time data types (time, timetz,
timestamp):
INSERT INTO <target-table> SELECT * FROM '<external-table>' USING (format 'fixed' layout (c1 int bytes 4 , c2 time 12HOUR DecimalDelim ',' bytes 12, c3 numeric(10,2) bytes 11) ); INSERT INTO <target-table> SELECT * FROM '<external-table>' USING (format 'fixed' layout (c1 int bytes 4 , c2 time timeDelim '-' DecimalDelim ',' bytes 12, c3 numeric(10,2) bytes 11) ); INSERT INTO <target-table> SELECT * FROM '<external-table>' USING (format 'fixed' layout (c1 int bytes 4 , c2 time timeDelim '-' ',' bytes 12, c3 numeric(10,2) bytes 11) ); INSERT INTO <target-table> SELECT * FROM '<external-table>' USING (format 'fixed' layout (c1 int bytes 4 , c2 time 12HOUR '-' DecimalDelim ',' bytes 12, c3 numeric(10,2) bytes 11) ); INSERT INTO <target-table> SELECT * FROM '<external-table>' USING (format 'fixed' layout (c1 int bytes 4 , c2 time 12HOUR '-' ',' bytes 12, c3 numeric(10,2) bytes 11) ); INSERT INTO <target-table> SELECT * FROM '<external-table>' USING (format 'fixed' layout (c1 int bytes 4 , c2 time 12HOUR timeDelim '-' DecimalDelim ',' bytes 12, c3 numeric(10,2) bytes 11) ); INSERT INTO <target-table> SELECT * FROM '<external-table>' USING (format 'fixed' layout (c1 int bytes 4 , c2 time 12HOUR timeDelim '-' ',' bytes 12, c3 numeric(10,2) bytes 11) )
- The following example is for the numeric data type: