in postgresql, how to preserve spaces in char

Question

how to preserve spaces in char? I create 2 tables ,

create table test_1 (a int, b char(10)) ;
create table test_2 (a int, b varchar(255));

and insert one row into test_1

insert into test_1 values (1 ,'     ');


insert into test_2 select * from test_1;
select a, length(b) from test_2;

and it returns

| a        | length(b)      |
| -------- | -------------- |
| 1        | 0              |

I expect bleow, like oracle does

| a        | length(b)      |
| -------- | -------------- |
| 1        | 10             |

is there any option can i try ?

Answer 1

There is no way to change this behavior.

Observe also the following:

CREATE TABLE test_1 (a int, b char(10));

INSERT INTO test_1 VALUES (1, 'x');

SELECT length(b) FROM test_1;

 length 
--------
      1
(1 row)

SELECT 'y' || b FROM test_1;

 ?column? 
----------
 yx
(1 row)

All this is working as required by the SQL standard.

Do yourself a favor and never use char. If you need the value to always have 10 characters, user a check constraint or a trigger that pads values with blanks.