flex-key-data-types.md
Assigning flex key data types
When the EnableBetterFlexTypeGuessing configuration parameter is ON (the default), COMPUTE_FLEXTABLE_KEYS produces more specific results.
Use the sample CSV data in this section to compare the results of using or not using the parameter:
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
Create a flex table and load this data:
=> CREATE FLEX TABLE trees();
=> COPY trees FROM '/home/dbadmin/tempdat/trees.csv' PARSER fcsvparser();
Compute keys and check the results with the default setting:
=> SELECT COMPUTE_FLEXTABLE_KEYS('trees');
COMPUTE_FLEXTABLE_KEYS
-----------------------------------------------
Please see public.trees_keys for updated keys
(1 row)
=> 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 EnableBetterFlexTypeGuessing to 0 (OFF), compute keys again, and compare the results:
=> 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)