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.

Improve this question
2
  • 1
    You're erroneously expecting that any DBMS actually fully supports the standard. None of them fully support it, and it's basically a waste of time trying to look for DBMS-agnostic code, just code to whichever product you are using. SQL Server uses sp_rename
    – Charlieface
    Commented Mar 17, 2022 at 14:47
  • I'm maintaining software that is using two products and would rather code agnostically but, alas, the world is nor perfect. I'll keep #ifdefing as usual.
    – vesperto
    Commented Mar 17, 2022 at 17:19

1 Answer 1

Reset to default
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.

Improve this answer
7
  • 1
    The standard (at least the draft of the 2011 version I have) does not have a RENAME column command. The <alter column definition> command (ALTER TABLE .. ALTER COLUMN ...`) does not have any rename option.
    – ypercubeᵀᴹ
    Commented Mar 17, 2022 at 15:13
  • I could not find rename either in the draft document. AFAIK this should be the standard ALTER TABLE org ALTER COLUMN deptnumb TO deptnum but very few DBMS seems to implement that.
    – Lennart - Slava Ukraini
    Commented Mar 17, 2022 at 15:38
  • Why do you think this should be the standard? I don't object but we usually don't get to decide ;)
    – ypercubeᵀᴹ
    Commented Mar 17, 2022 at 15:43
  • I vaguely remember seen it before so I checked with https://developer.mimer.com/sql-2016-validator/
    – Lennart - Slava Ukraini
    Commented Mar 17, 2022 at 15:54
  • 1
    @ypercubeᵀᴹ ISO/IEC 9075-1:1999 seems to say the same, there is no such command.
    – vesperto
    Commented Mar 17, 2022 at 17:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.