CAST to CHAR Cuts a Value to One Character on PostgreSQL
CAST(order_id AS CHAR) looks like a safe way to turn a number into text. On PostgreSQL CHAR without a length means one character, so order 123 comes back as '1'.
PostgreSQL reads CHAR without a length as CHAR(1) and cuts the value to fit. With a length, CHAR(10), it pads the value with spaces. MySQL accepts CHAR in CAST but neither VARCHAR nor TEXT, and MariaDB accepts VARCHAR but not TEXT.
What Each Engine Answered
The example
SELECT CAST(123 AS CHAR);
gave these answers:
| Engine | Answer |
|---|---|
| SQLite 3.45.1 | '123' |
| PostgreSQL 16.15 | '1' |
| MySQL 8.0.46 | '123' |
| MariaDB 10.11.14 | '123' |
These answers need SQLite 3.44 or later.
Every Form BareSQL Checks
BareSQL Portable checks 4 forms of this, and each was run on all four engines. yes means the engine ran it and the engines that ran it agreed, no means the engine refused it, and a value is what the engine answered where they disagreed.
| Form | SQLite | PostgreSQL | MySQL | MariaDB |
|---|---|---|---|---|
CHAR |
'123' |
'1' |
'123' |
'123' |
CHAR(n) |
'123' |
'123 ' |
'123' |
'123' |
VARCHAR |
yes | yes | no | yes |
TEXT |
yes | yes | no | no |
What to Write Instead
This form answers the same on every engine:
SELECT CONCAT(123);
| Engine | Answer |
|---|---|
| SQLite 3.45.1 | '123' |
| PostgreSQL 16.15 | '123' |
| MySQL 8.0.46 | '123' |
| MariaDB 10.11.14 | '123' |
CONCAT(x) with one argument turns a value into text on all four engines (SQLite from version 3.44).
What BareSQL Portable Reports
Saved as example.sql, the example gets this from baresql lint --all:
$ baresql lint --all example.sql
example.sql:1:8: warning: CAST(x AS CHAR) keeps one character on PostgreSQL, where CHAR means CHAR(1). [cast-to-text]
CAST(123 AS CHAR)
CONCAT(x) with one argument turns a value into text on all four engines (SQLite from version 3.44).
1 problem: 1 warning.
baresql rules cast-to-text prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.