CHAR Columns Come Back Padded With Spaces on PostgreSQL

An order status kept in a CHAR(10) column compares as expected in SQL on every engine. In the application code, though, a check for paid orders fails on PostgreSQL alone.

A CHAR(5) column holding 'a' returns 'a ' on PostgreSQL. Comparisons there ignore the padding, so WHERE c = 'a' still matches, but the spaces reach the application and CONCAT. MySQL and MariaDB strip trailing spaces when they read CHAR values, and SQLite does not apply the length at all.

What Each Engine Answered

The example

CREATE TABLE t (c CHAR(5));
INSERT INTO t VALUES ('a');
SELECT c FROM t;

gave these answers:

What to Write Instead

This form answers the same on every engine:

CREATE TABLE t (c VARCHAR(5));
INSERT INTO t VALUES ('a');
SELECT c FROM t;

Use VARCHAR(n) unless every value has exactly n characters.

What BareSQL Portable Reports

Saved as example.sql, the example gets this from baresql lint --all:

$ baresql lint --all example.sql
example.sql:1:19: info: PostgreSQL pads CHAR(n) values with spaces up to n characters; SQLite, MySQL and MariaDB give them back as stored. [char-padding]
    CHAR(5)
    Use VARCHAR(n) unless every value has exactly n characters.

1 problem: 1 note.

baresql rules char-padding prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.