CHAR vs VARCHAR

Hi Bernard,

The Vertica version of VARCHAR does conform to the SQL-92 specification.
Use a CHAR column if you want trailing spaces to be ignored. (See Jira VER-29333)

Vertica behaviour is compatible with the documentation:
"CHAR is conceptually a fixed-length, blank-padded string. Any trailing blanks (spaces) are removed on input, and only restored on output."
VARCHAR is a variable-length character data type [..] Values can include trailing spaces.

create table example(f1 char(10), f2 char(20), f3 varchar(20), f4 varchar(20));
insert into example values('abc ','abc','abc ','abc');
select f1=f2, f1=f3, f1=f4, f3=f4 from example;
t | f | t | f

Hi Moshe ,

As you say and the documentation too : "CHAR is conceptually a fixed-length, blank-padded string. Any trailing blanks (spaces) are removed on input, and only restored on output. "

But if I insert "XYZ " in a CHAR(10) column col1 , I get
select "|"||col1||"|" from test
|XYZ|

If I do the same on "another" database engine , I get
select "|"||col1||"|" from test
|XYZ_______| (with 7 spaces "_")
(always 10 characters whatever I have entered)

Regards
Bernard

The string concatenation operator (||) will cast everything to VARCHAR getting rid of the trailing spaces...

dbadmin=> create table test (col1 char(100));
CREATE TABLE

dbadmin=> insert into test values ('XYZ');
 OUTPUT
--------
      1
(1 row)

dbadmin=> select col1, col1 || '|' from test;
                                                 col1                                                 | ?column?
------------------------------------------------------------------------------------------------------+----------
 XYZ                                                                                                  | XYZ|
(1 row)

Notice when I query just col1 without a concat operator, I see all of the trailing spaces as expected...

dbadmin=> create table test2 as select col1, col1 || '|' from test;
CREATE TABLE

dbadmin=> \d test2
                                      List of Fields by Tables
 Schema | Table |   Column   |     Type     | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+------------+--------------+------+---------+----------+-------------+-------------
 public | test2 | col1       | char(100)    |  100 |         | f        | f           |
 public | test2 | "?column?" | varchar(101) |  101 |         | f        | f           |
(2 rows)

Note that the second column is created as a VARCHAR.

See:
https://my.vertica.com/docs/8.1.x/HTML/index.htm#Authoring/SQLReferenceManual/LanguageElements/Operators/StringConcatenationOperators.htm