Date: 2014-09-12 14:20:56 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: -- development
CC: ajdamico, akkaran046, @hannesmuehleisen, jonathan, @PedroTadim, publiwebmaxter, webhostinguk

Last updated: 2019-04-30 12:36:00 +0200

Comment 20149

Date: 2014-09-12 14:20:56 +0200
From: Martin van Dinther <<martin.van.dinther>>

User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:32.0) Gecko/20100101 Firefox/32.0
Build Identifier:

SQL syntax ALTER TABLE oldtablename RENAME TO newtablename is currently not supported.

It is very useful in practise as the workaround (CREATE TABLE newtablename AS SELECT * FROM oldtablename WITH DATA; add all the constraints, add all the indexes; add all the privileges; etc. DROP TABLE oldtablename CASCADE; etc.) is a lot of (manual) work, a lot of statements, error prone and not efficient/fast.

Reproducible: Always

Steps to Reproduce:

CREATE TABLE tmp (col1 int);
ALTER TABLE tmp RENAME TO tmp_new;
DROP TABLE tmp_new CASCADE;
DROP TABLE tmp CASCADE;

Actual Results:

Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased)
Database: MonetDB v11.20.0 (unreleased), 'demo'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>CREATE TABLE tmp (col1 int);
operation successful (13.574ms)
sql>ALTER TABLE tmp RENAME TO tmp_new;
syntax error, unexpected RENAME, expecting DROP or SET or ALTER or ADD in: "alter table tmp rename"
sql>DROP TABLE tmp_new CASCADE;
DROP TABLE: no such table 'tmp_new'
sql>DROP TABLE tmp CASCADE;
operation successful (3.722ms)
sql>

Expected Results:

sql>CREATE TABLE tmp (col1 int);
operation successful (13.574ms)
sql>ALTER TABLE tmp RENAME TO tmp_new;
operation successful (3.574ms)
sql>DROP TABLE tmp_new CASCADE;
operation successful (3.722ms)
sql>DROP TABLE tmp CASCADE;
DROP TABLE: no such table 'tmp'
sql>

Alternative SQL syntax is: RENAME TABLE oldtablename newtablename

Comment 21651

Date: 2015-12-09 14:01:49 +0100
From: @hannesmuehleisen

+1, SQLite uses ALTER TABLE oldname RENAME TO newname;

Comment 21652

Date: 2015-12-09 14:05:01 +0100
From: Anthony Damico <>

hi, this functionality would be really useful for MonetDBLite seamlessly working with RSQLite code. i'd appreciate it if this might be added at some point in the future.. thanks!!

Comment 25259

Date: 2017-04-21 17:54:29 +0200
From: Martin van Dinther <<martin.van.dinther>>

*** Bug #3888 has been marked as a duplicate of this bug. ***

Comment 26497

Date: 2018-06-20 19:09:23 +0200
From: jonathan

+1

Comment 26669

Date: 2018-11-07 10:57:52 +0100
From: @PedroTadim

A new SQL statement will be available in the next feature release of MonetDB for renaming tables:

ALTER TABLE [IF EXISTS] [schema.]oldname RENAME TO newname;

However it's still not possible to alter the table's schema. We came with a possible solution for this issue and we will come back to it later.

Comment 26962

Date: 2019-04-18 18:06:12 +0200
From: Martin van Dinther <<martin.van.dinther>>

Pedro added possibility to also change the schema of a table but with the syntax:
ALTER TABLE [ IF EXISTS ] qname SET SCHEMA ident;

For example:
CREATE TABLE sys.test1 (col1 int);
SELECT * FROM sys.test1;

ALTER TABLE IF EXISTS sys.test1 SET SCHEMA profiler;
SELECT * FROM profiler.test1;

ALTER TABLE IF EXISTS profiler.test1 SET SCHEMA json;
SELECT * FROM json.test1;

ALTER TABLE IF EXISTS json.test1 SET SCHEMA sys;
SELECT * FROM sys.test1;

DROP TABLE sys.test1;