Loading data into Vertica database from a file

[JimKnicely]

Hi,

You are correct about the error:

Code: Select all

dbadmin=> create table test (col1 varchar(1));
CREATE TABLE
dbadmin=> insert into test values ('AA');
ERROR 4800:  String of 2 octets is too long for type Varchar(1)

You sure you are using a flex table? Try:

Code: Select all

select is_flextable from tables where table_schema = 'staging' and table_name = 'subject_demographics';

Did you run a earlier load that only had a single octet?

Look at the keys to get the data type and size...

Code: Select all

select compute_flextable_keys('staging.subject_demographics');

[bwest@sdsc.edu]

Looks like your right. They do not appear to be flex tables. Here is what I am using to create flex tables:

Code: Select all

wellderly=> create flex table test_flex();
CREATE TABLE
wellderly=> select is_flextable from tables where table_name = 'test_flex';
 is_flextable 
--------------
 t
(1 row)

wellderly=> select compute_flextable_keys('public.test_flex_keys');
ERROR 6036:  Table "public.test_flex_keys" is not a flex table
wellderly=> \dt
                                          List of tables
          Schema           |                   Name                    | Kind  |  Owner  | Comment 
---------------------------+-------------------------------------------+-------+---------+---------
 public                    | test_flex                                 | table | dbadmin | 
 public                    | test_flex_keys                            | table | dbadmin | 
 staging                   | demographics                              | table | dbadmin | 
 staging                   | sg_advisor                                | table | dbadmin | 
 v_dbd_wellderly           | vs_deployment_projection_statements       | table | dbadmin | 
 v_dbd_wellderly           | vs_deployment_projections                 | table | dbadmin | 
 v_dbd_wellderly           | vs_deployments                            | table | dbadmin | 
 v_dbd_wellderly           | vs_design_tables                          | table | dbadmin | 
 v_dbd_wellderly           | vs_designs                                | table | dbadmin | 
 v_dbd_wellderly           | vs_output_deployment_status               | table | dbadmin | 
 v_dbd_wellderly_wellderly | vs_design_overrides                       | table | dbadmin | 
 v_dbd_wellderly_wellderly | vs_design_queries                         | table | dbadmin | 
 v_dbd_wellderly_wellderly | vs_design_queries_columns                 | table | dbadmin | 
 v_dbd_wellderly_wellderly | vs_design_queries_expressions             | table | dbadmin | 
 v_dbd_wellderly_wellderly | vs_design_queries_from                    | table | dbadmin | 
 v_dbd_wellderly_wellderly | vs_design_queries_join_predicates         | table | dbadmin | 
 v_dbd_wellderly_wellderly | vs_design_queries_joins                   | table | dbadmin | 
 v_dbd_wellderly_wellderly | vs_design_queries_subqueries              | table | dbadmin | 
 v_dbd_wellderly_wellderly | vs_output_event_history                   | table | dbadmin | 
 v_dbd_wellderly_wellderly | vs_output_override_projections            | table | dbadmin | 
 v_dbd_wellderly_wellderly | vs_output_projection_columns              | table | dbadmin | 
 v_dbd_wellderly_wellderly | vs_output_projection_statements           | table | dbadmin | 
 v_dbd_wellderly_wellderly | vs_output_projections                     | table | dbadmin | 
 v_dbd_wellderly_wellderly | vs_output_query_ideal_plan_features       | table | dbadmin | 
 v_dbd_wellderly_wellderly | vs_output_query_plan_costs                | table | dbadmin | 
 v_dbd_wellderly_wellderly | vs_output_query_projections               | table | dbadmin | 
 v_dbd_wellderly_wellderly | vs_projection_columns_encoding_storage    | table | dbadmin | 
 v_dbd_wellderly_wellderly | vs_segmented_projection_data_distribution | table | dbadmin | 
(28 rows)

It does seem that part of vertical think this is a flex table but another part of the system seems to have its doubts. Can you help me with the correct syntax to create a flex table?

Thanks,

bw

[JimKnicely]

Hi,

Vertica is barking at the int value and obviously confused. Are you sure your data does not have the pipe | in there somewhere? In some text?

[bwest@sdsc.edu]

Yes there are pipes '|' in the source document. I take it that these cannot exist even in a tab delimited file?

Regards,

bw