Add column location

{question}

Is it possible to add a new column in between existing columns in NuoDB table?  

For example:

create table employees
(Id bigint generated always as identity primary key NOT NULL,
Name char(36) NOT NULL,
City char(36) NOT NULL,
Location varchar(255) NOT NULL);

How is a column "Mobile" added between "Name" and "City"?

{question}

 

{answer}

Adding column location isn't directly supported by NuoDB at this time.   There is no syntax in NuoDB that supports this type of command:

alter table employees add column Mobile varchar(30) AFTER Name;

Also, there is no syntax that supports a new column to be inserted in a particular position in an existing table. Performance-wise, the position of a column in a table makes no difference in NuoDB.

When a column is added to an existing table, it always added after the last existing column:

SQL> alter table employees add column Mobile varchar(30);
SQL> show table employees Tables named EMPLOYEES Found table EMPLOYEES in schema USER Fields:
ID bigint
Nullable: No
Generator: EMPLOYEES$IDENTITY_SEQUENCE Generated Always
NAME char(36)
Nullable: No
CITY char(36)
Nullable: No
LOCATION varchar(255)
Nullable: No
MOBILE varchar(30)
Primary Index: EMPLOYEES..PRIMARY_KEY on field: ID

 

However, there is a popular workaround that addresses this issue nicely:

1. Create a new table, use the exact order of columns that you need.

2. Copy the data from the original table to the new table.

3. Drop the original table.

4. Rename the new table as the original table.

SQL> create table employees2
 (Id bigint generated always as identity primary key NOT NULL,
 Name char(36) NOT NULL,
 Mobile varchar(30),
 City char(36) NOT NULL,
 Location varchar(255) NOT NULL);
SQL> show table employees2 Tables named EMPLOYEES2 Found table EMPLOYEES2 in schema USER Fields:
ID bigint
Nullable: No
Generator: EMPLOYEES2$IDENTITY_SEQUENCE Generated Always
NAME char(36)
Nullable: No
MOBILE varchar(30)
CITY char(36)
Nullable: No
LOCATION varchar(255)
Nullable: No
Primary Index: EMPLOYEES2..PRIMARY_KEY on field: ID
SQL> insert into employees2(NAME,MOBILE,CITY,LOCATION) select NAME,MOBILE,CITY,LOCATION from employees;

SQL> select * from employees2; ID NAME MOBILE CITY LOCATION
--- ----- ------- -------- --------- 1 Paul <null> New York USA
2 David <null> London Europe
SQL> drop table employees;

SQL> rename table employees2 to employees;

SQL> select * from employees; ID NAME MOBILE CITY LOCATION
--- ----- ------- -------- --------- 1 Paul <null> New York USA
2 David <null> London Europe

 

You may find more information and examples for "ALTER TABLE" here.

 

{answer}

Have more questions? Submit a request

Comments