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?
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.
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
col_name. The default is to add the column last. You can also use
MODIFYoperations to reorder columns within a table.
I googled for this for PostgreSQL but it seems to be impossible.
ALTER TABLE tablename ADD column_name57 INT AFTER column_name56
ALTER TABLE table_name ADD COLUMN column_name57 INTEGER AFTER column_name56
ALTER TABLE table_name ADD COLUMN column_name integer
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
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;
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.
I tried to alter the table like so:
table_name add column column_name after column column_name;
column_name is the new column name, the second
column_name is the existing column where you plan to insert into after.