flex-key-data-types.md
Assigning flex key data types
Use the sample CSV data in this section to compare the results of using or not using the EnableBetterFlexTypeGuessing configuration parameter. When the parameter is ON, the function determines key non-string data types in your map data more accurately. The default for the parameter is 1 (ON).
Year,Quarter,Region,Species,Grade,Pond Value,Number of Quotes,Available
2015,1,2 - Northwest Oregon & Willamette,Douglas-fir,1P,$615.12 ,12,No
2015,1,2 - Northwest Oregon & Willamette,Douglas-fir,SM,$610.78 ,12,Yes
2015,1,2 - Northwest Oregon & Willamette,Douglas-fir,2S,$596.00 ,20,Yes
2015,1,2 - Northwest Oregon & Willamette,Hemlock,P,$520.00 ,6,Yes
2015,1,2 - Northwest Oregon & Willamette,Hemlock,SM,$510.00 ,6,No
2015,1,2 - Northwest Oregon & Willamette,Hemlock,2S,$490.00 ,14,No
To compare the data type assignment results, complete the following steps:
-
Save the CSV data file (here, as
trees.csv). -
Create a flex table (
trees) and loadtrees.csvusing thefcsvparser:=> CREATE FLEX TABLE trees(); => COPY trees FROM '/home/dbadmin/tempdat/trees.csv' PARSER fcsvparser(); -
Use
COMPUTE_FLEXTABLE_KEYSwith thetreesflex table.=> SELECT COMPUTE_FLEXTABLE_KEYS('trees'); COMPUTE_FLEXTABLE_KEYS ----------------------------------------------- Please see public.trees_keys for updated keys (1 row) -
Query the
trees_keystable output.:=> SELECT * FROM trees_keys; key_name | frequency | data_type_guess ------------------+-----------+----------------- Year | 6 | Integer Quarter | 6 | Integer Region | 6 | Varchar(66) Available | 6 | Boolean Number of Quotes | 6 | Integer Grade | 6 | Varchar(20) Species | 6 | Varchar(22) Pond Value | 6 | Numeric(8,3) (8 rows) -
Set the
EnableBetterFlexTypeGuessingparameter to 0 (OFF). -
Call
COMPUTE_FLEXTABLE_KEYSwith thetreesflex table again. -
Query the
trees_keystable to compare thedata_type_guessvalues with the previous results. Without the configuration parameter set, all of the non-string data types are VARCHARS of various lengths:=> SELECT * FROM trees_keys; key_name | frequency | data_type_guess ------------------+-----------+----------------- Year | 6 | varchar(20) Quarter | 6 | varchar(20) Region | 6 | varchar(66) Available | 6 | varchar(20) Grade | 6 | varchar(20) Number of Quotes | 6 | varchar(20) Pond Value | 6 | varchar(20) Species | 6 | varchar(22) (8 rows) -
To maintain accurate results for non-string data types, set the
EnableBetterFlexTypeGuessingparameter back to 1 (ON).
For more information about the EnableBetterFlexTypeGuessing configuration parameter, see EnableBetterFlexTypeGuessing.