| -- Note field "col_null" is not in field location list |
| -- NULL value is NOT defined |
| DROP EXTERNAL TABLE IF EXISTS tbl_ext_fixedwidth; |
| NOTICE: foreign table "tbl_ext_fixedwidth" does not exist, skipping |
| CREATE READABLE EXTERNAL TABLE tbl_ext_fixedwidth ( |
| s1 char(10), s2 varchar(10), s3 text, |
| col_empty character(5),col_null character varying(5) ) |
| LOCATION ('file://@hostname@@abs_srcdir@/data/fixedwidth_miss_col_null.tbl') |
| FORMAT 'CUSTOM' (formatter='fixedwidth_in', |
| s1='10',s2='10', s3='10', col_empty='5'); |
| -- Note field "col_null" is always loaded with null value |
| -- regardless null string and preserve_blanks settings. |
| select * from tbl_ext_fixedwidth where col_null is null order by s1; |
| ERROR: mismatch in column length specification |
| DETAIL: The fixed width formatter requires a length specification for each one of the external table columns being used (currently 5, however format string has 4). |
| CONTEXT: External table tbl_ext_fixedwidth |
| -- This is true even using "LIKE other table" syntax when creating ext table |
| -- Create heap table "missing_col_null", which has default value 'NULL' for column "col_null" |
| DROP TABLE IF EXISTS heap_col_null; |
| NOTICE: table "heap_col_null" does not exist, skipping |
| CREATE TABLE heap_col_null ( |
| s1 char(10), s2 varchar(10), s3 text, |
| col_empty character(5),col_null character varying(5) default 'NULL' ); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 's1' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| \d heap_col_null |
| Table "public.heap_col_null" |
| Column | Type | Collation | Nullable | Default |
| -----------+-----------------------+-----------+----------+--------------------------- |
| s1 | character(10) | | | |
| s2 | character varying(10) | | | |
| s3 | text | | | |
| col_empty | character(5) | | | |
| col_null | character varying(5) | | | 'NULL'::character varying |
| Distributed by: (s1) |
| |
| -- Create ext table like "heap_col_null" |
| -- Note field "col_null" is not in field location list |
| DROP EXTERNAL TABLE IF EXISTS tbl_ext_fixedwidth; |
| CREATE READABLE EXTERNAL TABLE tbl_ext_fixedwidth (LIKE heap_col_null) |
| LOCATION ('file://@hostname@@abs_srcdir@/data/fixedwidth_miss_col_null.tbl') |
| FORMAT 'CUSTOM' (formatter='fixedwidth_in', |
| s1='10',s2='10', s3='10', col_empty='5'); |
| -- Describe ext table, notice column "col_null" does not have default value |
| \d tbl_ext_fixedwidth |
| Foreign table "public.tbl_ext_fixedwidth" |
| Column | Type | Collation | Nullable | Default | FDW options |
| -----------+-----------------------+-----------+----------+---------+------------- |
| s1 | character(10) | | | | |
| s2 | character varying(10) | | | | |
| s3 | text | | | | |
| col_empty | character(5) | | | | |
| col_null | character varying(5) | | | | |
| FDW options: (formatter 'fixedwidth_in', s1 '10', s2 '10', s3 '10', col_empty '5', format 'custom', format_type 'b', location_uris 'file://@hostname@@abs_srcdir@/data/fixedwidth_miss_col_null.tbl', execute_on 'ALL_SEGMENTS', log_errors 'f', encoding '6', is_writable 'false') |
| |
| -- Note field "col_null" is loaded with null value |
| select * from tbl_ext_fixedwidth where col_null is null order by s1; |
| ERROR: mismatch in column length specification |
| DETAIL: The fixed width formatter requires a length specification for each one of the external table columns being used (currently 5, however format string has 4). |
| CONTEXT: External table tbl_ext_fixedwidth |
