change / rename column if exists mariadb

Question

I couldn't remane my column if it existed, this is what i tried (i'm adding the different lines tested in same ALTER my_table to summarize) :

ALTER TABLE my_table
  RENAME IF EXISTS column_name TO column_name_new column_definition;
  RENAME IF EXISTS column_name column_name_new column_definition;
  RENAME COLUMN column_name TO column_name_new column_definition;
  RENAME COLUMN column_name column_name_new column_definition;

everything returned me this error : ALTER TABLE my_table RENAME COLUMN column_name TO column_name_new Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COLUMN column_name TO column_name_new' at line 2 0.000 sec

Note : column_definition correspond to the name /data type, with the optional constraint, default value, security.

Answer 1

https://mariadb.com/kb/en/alter-table/ using this as the reference, I found that RENAME (COLUMN) IF EXISTS well doesn't exist.

This is the solution present in the documentation :

 CHANGE [COLUMN] [IF EXISTS] old_col_name new_col_name column_definition [FIRST|AFTER col_name]

Example that worked :

ALTER TABLE my_table
  CHANGE COLUMN IF EXISTS column_name column_name_new TYPE DEFAULT VALUE;

This code can be reexecuted without error, tested with mariaDB 10.3.

Answer 2

ALTER TABLE table_name CHANGE old_col_name new_col_name varchar(10);

You must include the type of the new column even if it has not changed.

To rename the table itself, but not the COLUMN, use RENAME TABLE old_name TO new_name;

Tested in mariadb v. 15.1