CHAR data type and missing spaces question

[beth]

Hello!

I'm just starting out with Vertica and am glad that I found this site! I'll probably have a lot of questions and hope that I can get help from the community Thanks in advance.

My first question has to do with the CHAR vs VARCHAR data types. Is the following expected behavior? Notice that the CHAR data type ignores any spaces that are inserted:

Code: Select all

dbadmin=> insert into chartest values ('BETH', 'BETH');
 OUTPUT 
--------
      1
(1 row)

dbadmin=> insert into chartest values ('BETH  ', 'BETH  ');
 OUTPUT 
--------
      1
(1 row)

dbadmin=> select '*' || col_char || '*' col_char
dbadmin-> , length(col_char)
dbadmin-> , '*' || col_varchar || '*' col_varchar
dbadmin-> , length(col_varchar) 
dbadmin-> from chartest;
 col_char | length | col_varchar | length 
----------+--------+-------------+--------
 *BETH*   |      4 | *BETH*      |      4
 *BETH*   |      4 | *BETH  *    |      6
(2 rows)

Check out the second row. What happened to the 2 spaces after my name in the col_char column?

[scutter]

Hi Beth,

This is working as documented. The documentation for LENGTH() says:

"Strips the padding from CHAR expressions but not from VARCHAR expressions"

--Sharon

[beth]

Thanks for your reply, Sharon.

I now understand that about length, but what about how the col_char column is shown. The documentation says this about the CHAR data type:

CHAR is conceptually a fixed-length, blank-padded string. Any trailing blanks (spaces) are removed on input, and only restored on output.

If the spaces are restored on output, why isn't my output *BETH * in the col_char column?

[scutter]

Hi Beth,

Good questions. The padding is done on "final output":

"As opposed to some other SQL implementations, HP Vertica keeps CHAR strings unpadded internally, padding them only on final output. So converting a CHAR(3) 'ab' to VARCHAR(5) results in a VARCHAR of length 2, not one with length 3 including a trailing space."

In your example, you are concatenating '*' so the value isn't padded because it's not the final output.

Here's an example that shows that the padding is there when just the column is selected.

Code: Select all

dbadmin=> create table chartest(c1 char(8), c2 varchar(8));
CREATE TABLE
dbadmin=> insert into chartest values('BETH  ', 'BETH  ');
 OUTPUT 
--------
      1
(1 row)

dbadmin=> commit;
COMMIT
dbadmin=> \a
Output format is unaligned.
dbadmin=> select * from chartest;
c1|c2
BETH    |BETH  
(1 row)
dbadmin=> select '*'||c1||'*', '*'||c2||'*' from chartest;
?column?|?column?
*BETH*|*BETH  *
(1 row)
dbadmin=> select ('*'||c1||'*')::char(8), '*'||c2||'*' from chartest;
?column?|?column?
*BETH*  |*BETH  *
(1 row)

[beth]

Wow, that is weird. So, lesson is if I want spaces at the end of a string I should use varchar. That way I can concatenate a string to the end and have the spaces before the string concatenated Thanks for your explanation!