VARCHAR Without a Length Fails on MySQL and MariaDB
Schemas written for PostgreSQL often declare names and notes as plain VARCHAR, with no length. MySQL and MariaDB refuse that CREATE TABLE, since they want a length on every VARCHAR column.
PostgreSQL reads VARCHAR without a length as text of any length, and SQLite ignores lengths anyway.
What Each Engine Answered
The example
CREATE TABLE t (x VARCHAR);
SELECT COUNT(*) FROM t;
gave these answers:
| Engine | Answer |
|---|---|
| SQLite 3.45.1 | 0 |
| PostgreSQL 16.15 | 0 |
| MySQL 8.0.46 | error: You have an error in your SQL syntax ... near ')' at line 1 |
| MariaDB 10.11.14 | error: You have an error in your SQL syntax ... near ')' at line 1 |
What to Write Instead
This form answers the same on every engine:
CREATE TABLE t (x VARCHAR(255));
SELECT COUNT(*) FROM t;
| Engine | Answer |
|---|---|
| SQLite 3.45.1 | 0 |
| PostgreSQL 16.15 | 0 |
| MySQL 8.0.46 | 0 |
| MariaDB 10.11.14 | 0 |
Give a length, VARCHAR(255), or use TEXT for long values.
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: error: VARCHAR without a length works on SQLite and PostgreSQL; MySQL and MariaDB require one. [varchar-length]
VARCHAR
Give a length, VARCHAR(255), or use TEXT for long values.
1 problem: 1 error.
baresql rules varchar-length prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.