Can we add a column at a specific position to a table?

Question

In the context of this exercise, can we add a column at a specific position to a table?

Answer

No, unfortunately, you cannot specify what position to add a column to a table.

By default, a new column will always be added at the end of the table. For most intents and purposes, this should not affect much, since you can always select the columns in any order, for instance, like

SELECT col3, col1, col2

If column order is very important, then an alternative is to create a new table and add the columns in the specific order they should appear.

30 Likes

Ok, that was useful!

13 Likes

How would the order/index of columns affect anything?

To me it seems like something better left for the implementation. From the user (don’t want to hard-code index) and theoretical (sets and relations) standpoint this does not seem meaningful.

Besides a view seem like a much better idea than a whole new table (the cost of copy?!)

I more detailed answer would be appreciated, perhaps clarifying

  • how is the data stored?
  • how does ALTER work and what is the cost?
  • link/example/reference to future lesson about making new table/view and the cost of that
13 Likes

I don’t know about SQLite but it is possible in MySQL to add a new column in between the existing column. For example, are given below

Alter table customers
ADD column gender ENUM(‘M’,‘F’) after last_name;

Here in the above command here I use after clause that means the new column will add next to the “last_name” column.

52 Likes

Thanks for your answer! It is very helpful your insight! :grinning:

2 Likes

Good to know, thank you!

2 Likes

Good to know, it is very helpful.

2 Likes

So, after i created the column i was wondering…how do i add data and VALUES to it.
can someone give an example

2 Likes

moving to Update statement. you will see.

2 Likes

Regarding this exercise, how would I go about adding more than one column at a time? I was trying something along these lines…


… but it doesn’t seem to work.
5 Likes

Thanks for the answer. Useful!

You have to repeat the full command set, i.e.,
ALTER TABLE celebs
ADD COLUMN sex TEXT;
ALTER TABLE celebs
ADD COLUMN marital_status TEXT;

1 Like

I think SQLite doesn’t support adding multiple columns with a single statement.

But in MySQL, we can add multiple columns with a single statement.
Example-
ALTER TABLE celebs
ADD COLUMN (gender TEXT, marital_status TEXT);

The above statement will add two new columns to the MySQL database table.

Note: In this case, the AFTER clause will not work. I tried adding multiple columns at a specific position but it didn’t work. The AFTER clause is working when adding only one column.

:grinning:

8 Likes

Also, for adding a column at the starting position, we can use the specifier ‘FIRST’.
Example-
ALTER TABLE customers
ADD COLUMN full_name TEXT FIRST;

The above statement will add the column in the first position.
:wink:

3 Likes

That is useful, thanks man.
Makes me think MySQL is more flexible in terms of “layout”, I’ll have to look into it.

1 Like

Also great to know that we could define the type as ENUM like in java :slight_smile:

…more like ‘BETTER’…

Awesome, thank you :slight_smile:

Yes, it is possible to add a column at a specific position to a table in SQL using the ALTER TABLE statement with the ADD COLUMN clause. However, the specific syntax for adding a column at a specific position may vary depending on the database management system (DBMS) you are using.

In general, you can add a column to a specific position in a table by specifying the column’s name and data type, as well as the position where the column should be inserted. Here’s an example syntax that works for some common SQL database management systems:

-- Add a new column to a table at a specific position
ALTER TABLE my_table ADD COLUMN new_column data_type AFTER column_name;

In this syntax, replace “my_table” with the name of the table you want to add the column to, “new_column” with the name of the new column, “data_type” with the data type of the new column, and “column_name” with the name of the column that the new column should be inserted after.

Note that the exact syntax for specifying the position of the new column may differ between different SQL database management systems, so it’s important to consult your database’s documentation for the correct syntax. Additionally, adding a column to a specific position may not be supported by all database management systems.

5 Likes

I’m guessing that it’s rare professionally to actually create data tables from scratch using SQL?

1 Like