How to insert columns at a specific position in existing table?

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?

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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.

Method 2

Try this

ALTER TABLE tablename ADD column_name57 INT AFTER column_name56

See here

Method 3

ALTER TABLE table_name ADD COLUMN column_name57 INTEGER AFTER column_name56

Method 4

ALTER TABLE table_name ADD COLUMN column_name integer

Method 5

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

Method 6

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;

Method 7

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.

Method 8

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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating