ALTER TABLE Changes a Column One Way on PostgreSQL and Another on MySQL

A migration widens a column with ALTER TABLE products MODIFY name VARCHAR(80), and it runs on MySQL and MariaDB. PostgreSQL wants ALTER COLUMN name TYPE VARCHAR(80), and SQLite can’t change a column’s type at all.

SQLite’s ALTER TABLE renames tables and columns and adds and drops columns, one action per statement. It can’t change a column’s type, default or NULL rule and has no ADD CONSTRAINT. PostgreSQL changes a column piece by piece with ALTER COLUMN ... TYPE, SET DEFAULT and SET NOT NULL. MySQL and MariaDB redefine the whole column with MODIFY or CHANGE, take ALTER COLUMN only for defaults, and refuse ALTER COLUMN ... TYPE. IF NOT EXISTS on ADD COLUMN works on PostgreSQL and MariaDB only.

ALTER TABLE t MODIFY b VARCHAR(40), run on four engines: SQLite 3.45.1 error: near "MODIFY": syntax error; PostgreSQL 16.15 error: syntax error at or near "MODIFY"; MySQL 8.0.46 no rows; MariaDB 10.11.14 no rows.

What Each Engine Answered

On a table made with

CREATE TABLE t (a INTEGER, b VARCHAR(20));

the example

ALTER TABLE t MODIFY b VARCHAR(40);

gave these answers:

Engine	Answer
SQLite 3.45.1	error: `near "MODIFY": syntax error`
PostgreSQL 16.15	error: `syntax error at or near "MODIFY"`
MySQL 8.0.46	no rows
MariaDB 10.11.14	no rows

Every Form BareSQL Checks

BareSQL Portable checks 21 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	What to write instead
`ALTER COLUMN TYPE`	no	yes	no	no	MySQL and MariaDB: MODIFY b VARCHAR(40). SQLite: rebuild the table.
`SET NOT NULL`	no	yes	no	no	MySQL and MariaDB: MODIFY the whole column with NOT NULL. SQLite: rebuild the table.
`DROP NOT NULL`	no	yes	no	no	MySQL and MariaDB: MODIFY the whole column without NOT NULL. SQLite: rebuild the table.
`SET DEFAULT`	no	yes	yes	yes	SQLite: rebuild the table, or give the default in the INSERT statements.
`DROP DEFAULT`	no	yes	yes	yes	SQLite: rebuild the table.
`MODIFY`	no	no	yes	yes	PostgreSQL: ALTER COLUMN b TYPE VARCHAR(40), with SET or DROP NOT NULL and DEFAULT as separate actions. SQLite: rebuild the table.
`CHANGE`	no	no	yes	yes	Rename with RENAME COLUMN b TO c, which all four engines take, and change the type separately.
`several actions`	no	yes	yes	yes	Write one ALTER TABLE per action.
`ADD CONSTRAINT`	no	yes	yes	yes	SQLite: CREATE UNIQUE INDEX for a unique key; other constraints need a rebuilt table.
`ADD INDEX`	no	no	yes	yes	Use CREATE INDEX t_a ON t (a), which all four engines take.
`DROP CONSTRAINT`	no	yes	yes	yes	SQLite: rebuild the table.
`DROP CHECK`	no	no	yes	no	PostgreSQL and MariaDB: DROP CONSTRAINT t_ck. SQLite: rebuild the table.
`DROP INDEX`	no	no	yes	yes	Use DROP CONSTRAINT on PostgreSQL, MySQL and MariaDB, and a rebuilt table on SQLite.
`ADD COLUMN IF NOT EXISTS`	no	yes	no	yes	Check the catalog for the column in the migration code before adding it.
`DROP COLUMN IF EXISTS`	no	yes	no	yes	Check the catalog for the column in the migration code before dropping it.
`DROP COLUMN CASCADE`	no	yes	yes	yes	Drop what depends on the column first, then the column.
`RENAME without TO`	no	no	yes	yes	Write RENAME TO u.
`RENAME without COLUMN`	yes	yes	no	no	Write RENAME COLUMN b TO c.
`IF EXISTS`	no	yes	no	yes	Check the catalog for the table in the migration code first.
`AFTER or FIRST`	yes	no	yes	yes	Leave the position out: new columns go last on every engine, and queries should name their columns anyway.
`ALGORITHM or LOCK`	no	no	yes	yes	Leave ALGORITHM and LOCK out, or keep them in the MySQL and MariaDB migration only.

What to Write Instead

Keep migrations that change a column in one file per engine. On SQLite, create a table with the new definition, copy the rows, drop the old table and rename the new one. What works everywhere: ADD COLUMN, RENAME COLUMN ... TO and RENAME TO, one per ALTER TABLE, and DROP COLUMN, which SQLite refuses for a column that is indexed or part of a key.

What BareSQL Portable Reports

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

$ baresql lint --all example.sql
example.sql:2:15: error: ALTER TABLE ... MODIFY works on MySQL and MariaDB; SQLite and PostgreSQL refuse it. [alter-column]
    MODIFY b VARCHAR(40)
    PostgreSQL: ALTER COLUMN b TYPE VARCHAR(40), with SET or DROP NOT NULL and DEFAULT as separate actions. SQLite: rebuild the table.

1 problem: 1 error.

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