0

So far I've seen ALTER TABLE RENAME, ALTER TABLE CHANGE and ALTER TABLE MODIFY.

What I'm trying to achieve is something like ALTER TABLE my_table RENAME col_old_name TO col_new_name, which can be a case of modifying a column.

SQLite goes with RENAME.
MariaDB and MySQL have all three.
Postgres goes with RENAME.
Oracle has RENAME and MODIFY.
Standards are unkown to SQL Server.

What is the database-agnostic way for renaming (or generally altering) a column?

Maybe I'm erroneously expecting the standard to cover DDL operations as well? I have code that works with multiple databases so would rather make the SQL part as portable as possible.

Paul White
  • 94,921
  • 30
  • 437
  • 687
vesperto
  • 135
  • 1
  • 2
  • 8

1 Answers1

1

I believe the standard is:

ALTER TABLE org ALTER COLUMN deptnumb TO deptnum;

When I doubt, I usually go to SQL-Validator. I tried the latest of all DBMS at Fiddle and as far as I can tell Firebird 3.0 was the only one accepting that syntax.

To add to your list DB2 (luw) uses:

ALTER TABLE org RENAME COLUMN deptnumb TO deptnum

DB2 documentation

FWIW, of the DBMS available at db<>fiddle:

  • DB2 Developer-C 11.1
  • MariaDB 10.6
  • MySQL 8.0
  • Oracle 21c
  • Postgres 14
  • SQLite 3.27
  • YugabyteDB 2.8

Accepts the syntax ALTER TABLE org RENAME COLUMN deptnumb TO deptnum

Whereas:

  • Firebird 3.0
  • SQL Server 2019

Does not accept it.

Lennart - Slava Ukraini
  • 23,842
  • 3
  • 34
  • 72