sql: column padding not preserved like in postgres

[knz]

tldr: postgres preserves the right-padding of padded columns in some cases, CockroachDB does not.

Start with the following SQL

> CREATE TABLE t(x CHAR(8));
> INSERT INTO t(x) VALUES ('hello');

The run SELECT x FROM t in crdb and pg.

PostgreSQL:

{"Type":"DataRow","Values":[{"text":"hello   "}]}

CockroachDB:

{"Type":"DataRow","Values":[{"text":"hello"}]}

(I found this discrepancy when running the row_description pgtest in both crdb and pg)

It's hard to see in a SQL shell because the whitespace does not show on the screen, but still.

Interestingly, the following query behaves differently:

SELECT ':' || x || ':' FROM t

This returns both a short :hello: in both pg and crdb.

[jordanlewis]

Postgres CHAR types have fixed-width display: https://www.postgresql.org/docs/9.1/datatype-character.html

This could be a good welcome-back issue for @arulajmani.

[smahable]

postgres 13.4 on aws aurora is having this problem.

column is 30 char. using length shows actual length
also '['||c||']' shows no right padded string ...

[knz]

@smahable can you explain more? What problem still exists?

[smahable]

@knz - this is issue in postgres, not in CockroachDB
as mentioned char(30) - with data less than 30- length function and even select shows issue

create table test(id int, c char(10) not null);
insert into test values(1,'x');
select length(c),'['||c||']' from test;

1 [x]