64

I created a table with 85 columns but I missed one column. The missed column should be the 57th one. I don't want to drop that table and create it again. I'm looking to edit that table and add a column in the 57th index.

I tried the following query but it added a column at the end of the table.

ALTER table table_name
Add column column_name57 integer

How can I insert columns into a specific position?

TrebledJ
  • 8,713
  • 7
  • 26
  • 48
PathmanKIP
  • 685
  • 1
  • 5
  • 10
  • 3
    Are you using MySQL or Postgres? Postgres can only put a new column at the end - which isn't a problem because the order of columns in a table is totally irrelevant. There is no need to put a column at a specific position. If you need the columns in specific order, just put them into the select in that order. –  Jan 24 '14 at 10:33
  • 7
    Column order is totally irrelevant from the DB server's point of view. But it's important to write code and structure databases so that everything is easy for humans to understand too. Sometimes, for clarity, it's nice to have columns in a human-friendly order. – osullic Nov 08 '17 at 15:16

9 Answers9

104

ALTER TABLE by default adds new columns at the end of the table. Use the AFTER directive to place it in a certain position within the table:

ALTER table table_name
    Add column column_name57 integer AFTER column_name56

From mysql doc

To add a column at a specific position within a table row, use FIRST or AFTERcol_name. The default is to add the column last. You can also use FIRST and AFTER in CHANGE or MODIFY operations to reorder columns within a table.

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

I googled for this for PostgreSQL but it seems to be impossible.

Community
  • 1
  • 1
AmazingDreams
  • 3,136
  • 2
  • 22
  • 32
4

Try this

ALTER TABLE tablename ADD column_name57 INT AFTER column_name56

See here

Nagaraj S
  • 13,316
  • 6
  • 32
  • 53
1

if you are saying ADD COLUMN column_name then it will throw error

u have to try 

 ALTER TABLE temp_name ADD My_Coumn INT(1) NOT NULL DEFAULT 1

remember if table already has few record and u have to create new column then either u have to make it nullable or u have to define the default value as I did in my query

Deepak Sharma
  • 4,124
  • 1
  • 14
  • 31
  • 1
    `alter table add column` **is** valid for Postgres and the question is still tagged with `postgresql` as well. –  Jan 24 '14 at 11:15
0
ALTER TABLE table_name ADD COLUMN column_name57 INTEGER AFTER column_name56
jahller
  • 2,705
  • 1
  • 28
  • 30
java seeker
  • 1,246
  • 10
  • 13
0
ALTER TABLE table_name ADD COLUMN column_name integer
andrey_sz
  • 751
  • 1
  • 13
  • 29
0
SET
    @column_name =(
    SELECT COLUMN_NAME
FROM
    information_schema.columns
WHERE
    table_schema = 'database_name' AND TABLE_NAME = 'table_name' AND ordinal_position = 56
);
SET
    @query = CONCAT(
        'ALTER TABLE `table_name` ADD `new_column_name` int(5) AFTER ',
        @column_name
    );
PREPARE
    stmt
FROM
    @query;
EXECUTE
    stmt;
DEALLOCATE
    stmt;
Anand agrawal
  • 492
  • 6
  • 25
0

Note: AFTER keywork doesn't work in SQL Server.

But there is one way you can do this. (Caution: This will re create the table, so choice is yours, it will re-create the table)

Steps:

** STEP 1:
-> SQL Server Management Studio -> Tools -> Options ->
-> Designers' Tab -> 'Table and Database Designers' menu ->
-> uncheck the option 'Prevent saving changes that require table re-creation'.

** STEP 2: 
-> Then go to your table -> right click -> 
-> choose 'Design'. In 'Design' mode drag the columns to order them.
KushalSeth
  • 3,265
  • 1
  • 26
  • 29
-2

As workaround one could consider the use of column renaming. I.e. add the new column at the end, and then until the new column is at the right position, add a temporary column for each column whose position is after the new column, copy the value from the old column to the temporary one, drop the old column and finally rename the temporary column.

see also: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1770086700346491686

Bob
  • 1
  • I'm not sure what this answer is trying to achieve. There are already correct answers that are far more useful than this and the linked post is not related to the question either. – Steve Jan 16 '20 at 16:42
  • 1
    Sorry. I did not point out that this post is only addressed to users whose DBMS doesn't support the AFTER keyword, e.g. Oracle. – Bob Jan 17 '20 at 14:12
  • Ah, that makes more sense. Thanks for letting me know. – Steve Jan 17 '20 at 14:33
-3

I tried to alter the table like so:

table_name add column column_name after column column_name;

The first column_name is the new column name, the second column_name is the existing column where you plan to insert into after.

ZGski
  • 2,398
  • 1
  • 21
  • 34
user6382558
  • 1