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:

  1. Save the CSV data file (here, as trees.csv).

  2. Create a flex table (trees) and load trees.csv using the fcsvparser:

    => CREATE FLEX TABLE trees();
    => COPY trees FROM '/home/dbadmin/tempdat/trees.csv' PARSER fcsvparser();
    
  3. Use COMPUTE_FLEXTABLE_KEYS with the trees flex table.

    => SELECT COMPUTE_FLEXTABLE_KEYS('trees');
                COMPUTE_FLEXTABLE_KEYS
    -----------------------------------------------
     Please see public.trees_keys for updated keys
    (1 row)
    
  4. Query the trees_keys table 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)
    
  5. Set the EnableBetterFlexTypeGuessing parameter to 0 (OFF).

  6. Call COMPUTE_FLEXTABLE_KEYS with the trees flex table again.

  7. Query the trees_keys table to compare the data_type_guess values 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)
    
  8. To maintain accurate results for non-string data types, set the EnableBetterFlexTypeGuessing parameter back to 1 (ON).

For more information about the EnableBetterFlexTypeGuessing configuration parameter, see EnableBetterFlexTypeGuessing.