How can I prevent Knex from inserting trailing spaces on PostgreSQL?

Question

I'm using Node and the packages pg and Knex to insert data on PostgreSQL. This is my source code:

var knex = require('knex')({
    client: 'pg',
    connection: 'postgres://postgres:52Ag98d5@localhost:5433/CRM'
});

var rows = [];
for(var i = 0; i < 20000; i++)
{
    rows.push({
        name: faker.Name.findName(),
        email: faker.Internet.email()
    });
}

knex.batchInsert('contact', rows)
    .then(() => {
        knex.destroy();
    });

It works great, except that the string fields are being filled with trailing spaces. I mean... If a field is character(50) and I fill it with 10 characters, it's going to insert 40 spaces at the end. See the highlight on pgAdmin:

I don't know whose fault this is. I don't know if this is because of Knex or because of the Node pg driver.

Any ideia how to prevent this behavior?

Answer 1

Reason is very simple. Character(50) is fixed-length datatype.

Character datatype:

╔════════════════════════╦════════════════════════════╗
║          Name          ║        Description         ║
╠════════════════════════╬════════════════════════════╣
║ character(n), char(n)  ║ fixed-length, blank padded ║
╚════════════════════════╩════════════════════════════╝

If the string to be stored is shorter than the declared length, values of type character will be space-padded; values of type character varying will simply store the shorter string.

Solution:

Use VARCHAR(50) instead.