Questions tagged [alter-table]
SQL statement used to alter an existing table object.
384 questions
69
votes
1
answer
35k
views
How do I swap tables in MySQL?
Suppose, I have a table foo, which contains some statistics that are computed every now and then. It is heavily used by other queries.
That's why I want to compute more recent statistics in foo_new ...
- 887
61
votes
5
answers
43k
views
Why does ALTER COLUMN to NOT NULL cause massive log file growth?
I have a table with 64m rows taking 4.3 GB on disk for its data.
Each row is about 30 bytes of integer columns, plus a variable NVARCHAR(255) column for text.
I added a a NULLABLE column with data-...
- 713
47
votes
5
answers
61k
views
I need to run VACUUM FULL with no available disk space
I have one table that is taking up close to 90% of hd space on our server. I have decided to drop a few columns to free up space. But I need to return the space to the OS. The problem, though, is ...
- 573
33
votes
2
answers
28k
views
Database "frozen" on ALTER TABLE
Our production environment just froze* this morning for a while when altering a table, adding a column actually.
Offending SQL:ALTER TABLE cliente ADD COLUMN topicos character varying(20)[];
* ...
- 1,029
33
votes
4
answers
138k
views
How to add a column with a foreign key constraint to a table that already exists?
I have the following tables,
CREATE TABLE users (id int PRIMARY KEY);
-- already exists with data
CREATE TABLE message ();
How do I alter messages table such that,
a new column called sender is ...
- 2,039
31
votes
4
answers
33k
views
Adding columns to production tables
What's the best way to add columns to large production tables on SQL Server 2008 R2? According to Microsoft's books online:
The changes specified in ALTER TABLE are implemented immediately. If the ...
- 609
29
votes
6
answers
56k
views
Alter table on live production databases
How do most "popular" (MySQL, Postgres...) database system handle altering tables on live production databases (like adding, deleting or changing the type of colums)?
I know the correct way is to ...
- 393
28
votes
3
answers
34k
views
Why would I ever prefer ALGORITHM=COPY to ALGORITHM=INPLACE?
Since MySQL 5.6 introduced online DDL, the ALTER TABLE command can optionally have either ALGORITHM=INPLACE or ALGORITHM=COPY specified. The overview of online DDL notes that, by default, INPLACE is ...
- 1,095
27
votes
4
answers
23k
views
How can I add a rowversion column to a large table with minimal downtime
Using SQL Server 2008 and later, I want to add a rowversion column to a large table however when I simply
ALTER TABLE [Tablename]
ADD Rowversion [Rowversion] NOT NULL
Then the table is unavailable ...
- 2,140
26
votes
2
answers
41k
views
Changing a column from NOT NULL to NULL - What's going on under the hood?
We have a table with 2.3B rows in it. We'd like to change a column from NOT NULL to NULL. The column is contained in one index (not the clustered or PK index). The data type isn't changing (it's an ...
- 2,012
20
votes
3
answers
43k
views
Modify all tables in a database with a single command
Is there a single or a one line command to modify all tables within a database. I would like to issue this command in every table within a database:
ALTER TABLE `table_name` CONVERT TO CHARACTER SET ...
- 301
18
votes
3
answers
87k
views
Reset IDENTITY value
I have a table with an IDENTITY column. While developing I delete the rows from time to time and add them again. But the IDENTITY values always kept increasing and didn't start from 1 when I added ...
- 333
18
votes
3
answers
5k
views
Memory Optimized Tables - can they really be so difficult to maintain?
I'm investigating the benefits of upgrading from MS SQL 2012 to 2014. One of the big selling points of SQL 2014 is the memory optimized tables, which apparently make queries super-fast.
I've found ...
- 2,943
17
votes
5
answers
56k
views
MySQL - fastest way to ALTER TABLE for InnoDB
I have an InnoDB table that I want to alter. The table has ~80M rows, and quit a few indices.
I want to change the name of one of the columns and add a few more indices.
What is the fastest way to ...
- 1,563
17
votes
2
answers
39k
views
How long will it take to rename table
How long will it take to rename a table with a size of 219 GB?
This is the specification of our server:
Memory: 65 GB
Available disk space: 266 GB
MySQL Ver 14.12 Distrib 5.0.77
Sorry for the ...
- 171
17
votes
3
answers
19k
views
ALTER TABLE on a large table with an indexed column
I have a large table with a VARCHAR(20) column, and I need to modify that to become a VARCHAR(50) column. Typically, performing an ALTER TABLE (adding a TINYINT) on this particular table takes about ...
- 1,342
17
votes
2
answers
10k
views
Add NOT NULL constraint to large table without table scan [duplicate]
Trying to add a NOT NULL constraint to a table with 1 billion rows. I cannot afford a table lock for more than a couple of seconds. Is there a way to prevent a full table scan during the alter table ...
- 173
16
votes
2
answers
14k
views
Does PostgreSQL optimize adding columns with non-NULL DEFAULTs?
When adding NOT NULL columns with a DEFAULT value - does PostgreSQL optimize this operation?
In case the table has n rows, a not-optimized alter-table-add-column would yield n writes of the default ...
- 565
16
votes
2
answers
13k
views
Is it safe to cancel a PostgreSQL ALTER TABLE query that is waiting on a lock?
We started an ALTER TABLE query hours ago and only recently realized (via pg_stat_activity) that it is waiting on a lock. We discovered the other query that is holding a lock on the table we want to ...
- 263
16
votes
3
answers
67k
views
Why does simple ALTER TABLE command take so long on table with full-text index?
I have a large (~67 million rows) name-value table that has full-text indexing on the DataValue column.
If I try to run the following command:
ALTER TABLE VisitorData ADD NumericValue bit DEFAULT 0 ...
- 161
16
votes
1
answer
19k
views
Why is adding a NOT NULL column with a default constraint instantaneous?
CREATE TABLE TestTab (ID INT IDENTITY(1,1), st nvarchar(100))
INSERT INTO TestTab (st) values ('a')
INSERT INTO TestTab (st) values ('b')
INSERT INTO TestTab (st) values ('c')
INSERT INTO TestTab (st)...
- 1,032
15
votes
2
answers
9k
views
How can I set a column to NOT NULL without locking the table during a table scan?
(former question was: Will Postgres use a multi-column index when setting multiple colums not null?)
Typically, when I set a column not null, if it doesn't have an index then I add it first, so that ...
- 827
15
votes
1
answer
49k
views
When changing the size of a nvarchar column, do I need to drop the unique index? And will the table get locked when recreating the index?
In our database a big table exists that more or less looks like this:
CREATE TABLE dbo.production_data
(
pd_id BIGINT PRIMARY KEY,
serial NVARCHAR(16) NOT NULL UNIQUE,
...
);
but now the ...
- 407
15
votes
3
answers
3k
views
Increase speed altering column on large table to NON NULL
I recently added a NULL-able bit column to a table which has close to 500 million rows. There's not a default on the column, however all inserts are specifying a value of 0 or 1, and I ran a one-time ...
- 293
14
votes
4
answers
4k
views
Fast alter column NVARCHAR(4000) to NVARCHAR(260)
I have a performance problem with very large memory grants handling this table with a couple of NVARCHAR(4000) columns. Thing is these columns are never larger than NVARCHAR(260).
Using
ALTER TABLE [...
- 141
14
votes
1
answer
3k
views
Is ALTER TABLE ... DROP COLUMN really a metadata only operation?
I've found several sources that state ALTER TABLE ... DROP COLUMN is a meta-data only operation.
Source
How can this be? Does the data during a DROP COLUMN not need to be purged from the underlying ...
- 5,570
14
votes
2
answers
20k
views
Can I rename the values in a MySQL ENUM column in one query?
Suppose I have a Database table with an ENUM('value_one','value_two').
I want to change that to an ENUM('First value','Second value'). I an currently doing that as follows:
ALTER TABLE `table` MODIFY ...
- 558
13
votes
4
answers
11k
views
Dropping a constraint (index) on a column
How can I modify the type on a table that has an index on it? I tried to do an alter column on an empty table to modify the type from date time to varchar(15) and got errors saying that it had ...
- 231
12
votes
1
answer
60k
views
Altering a column: null to not null
I'm having to change a column in MYSQL to NULL to NOT NULL, but I get the error excutar the syntax. I want to change only the last column, already performed some research but have not found the ...
- 231
12
votes
2
answers
16k
views
Quickly change NULL column to NOT NULL
I have a table with millions of rows and a column that allows NULL values. However no row currently has a NULL value for that column (I can verify this fairly quickly with a query). However when I ...
- 532
12
votes
2
answers
22k
views
Alter large table without downtime
I have a very large table, lets call it example, and I try to execute a little alter command on that table:
ALTER TABLE `example` ADD `example_column` TINYINT(1) NOT NULL DEFAULT 0;
Checking the ...
- 221
12
votes
2
answers
5k
views
Fastest way to change a 600GB table indexed key datatype from INT to BIGINT
I need to change a datatype from INT to BIGINT in a 600GB MySQL table. The column has a unique index. I might be good with unsigned INT, but I assume changing to that or BIGINT will be pretty much ...
- 979
11
votes
3
answers
54k
views
SQL set allowed values for a column
I want to make an ALTER TABLE expression which adds a new column and sets a default value and additionaly defines the allowed values for that column. It's a text column, and allowed should be only '...
- 213
11
votes
1
answer
16k
views
How to add a PostgreSQL 10 identity column to an existing table?
I'm reading about the new Postgres 10 identity column, and saw how it is used to replace SERIAL columns when CREATE TABLE.
Is it also possible to add an identity column to an existing table?
For ...
- 3,692
11
votes
1
answer
69k
views
What happens when you modify (reduce) a column's length?
Lets say I have two columns of type NUMBER (without precision, and scale) and VARCHAR(300). I saw that these columns are way too large for my data, so I want to modify them to NUMBER(11) and VARCHAR(...
- 310
11
votes
1
answer
7k
views
Adding nullable column to table costs more than 10 minutes
I have problems to add a new column on a table.
I tried to run it a couple of times, but after more than 10 minutes running, I decided to cancel the query because of lock time.
ALTER TABLE mytable ...
10
votes
2
answers
28k
views
alter column size take very long time?
I have a table with 45M rows (45 GB data space and 2GB Index space). I added a new column and it finished instantly.
alter table T add C char(25)
Then I found the size is too small so I run the ...
- 1,545
10
votes
1
answer
29k
views
How to set a NOT NULL column into NULL in PostgreSQL?
I'm new to PostgreSQL and that is why I'm asking this question. I have a table app_client_users and a column named client_id. Initially I set it as NOT NULL and now I would like to change it to allow ...
10
votes
5
answers
21k
views
How do I move all tables from one Postgres schema to another
I want to move all tables from schema xyz to the default schema public.
I can move individual tables with
ALTER TABLE table_name
SET SCHEMA public;
And I can get all tables with
SELECT ...
- 235
10
votes
2
answers
33k
views
Does ALTER TABLE - ADD COLUMN lock the table?
I have a question about the ALTER TABLE ... ADD COLUMN DDL statement.
On a Amazon RDS instance with MariaDB v10.2, I've noticed that INSERT statements complete and the rows are correctly inserted in ...
- 1,334
9
votes
5
answers
15k
views
Altering table schema takes too much time
I am using PostgreSQL database.
I have a table with millions of rows. I have a varchar column with 2000 size and i want to make it to 4000. I ran the alter table command, but it is taking too much ...
9
votes
1
answer
11k
views
Make ALTER TABLE wait for lock without blocking anything else
Lots of PostgreSQL ALTER TABLE commands, like adding a new column with a default value, have clever optimisations in the latest versions of PostgreSQL that allow them to execute basically instantly, ...
- 1,095
8
votes
2
answers
24k
views
Add column with default in postgresql without table level lock
Have such a problem - table with over 20mln rows.
When i add new column with default - postgresql lock table for over 40 minutes so my application stop working for this time.
So instead of
ALTER ...
- 183
8
votes
1
answer
24k
views
The ALTER TABLE statement conflicted with the CHECK constraint
I need to check datecom is less than datelivr
create table Commande
(
Numcom int identity primary key,
Datecom date,
Datelivr date,
Codecli int foreign key references clients (Codecli)
)
alter ...
- 83
8
votes
1
answer
3k
views
How to add a PostgreSQL 10 identity column to an existing table with rows?
I have a table with some rows and I try adding an identity column using the command:
ALTER TABLE sourceTable
ADD COLUMN ogc_fid int
GENERATED BY DEFAULT AS IDENTITY;
The ...
7
votes
4
answers
16k
views
SQL Server - Alter table vs drop and create
Our database is SQL Server 2008 R2. We have some tables that have some varchar(500) columns that I want to switch to datetime2 or bigint. I can guarantee all the data in the columns to be switched are ...
- 83
7
votes
3
answers
29k
views
How to change the datatype of a column from integer to money?
I am attempting to convert a PostgreSQL table column from integer to money, but I am receiving the error:
cannot cast type MyColumn to money
I have tried these two statements, but just haven't ...
- 71
7
votes
6
answers
68k
views
How To change the column order?
How To change the column order in sql server 2008 R2?
Eg: My table order is Eid------Ename-----Esalary
Know i want this format like Eid---Salary----Ename
Thanks.
user36326
7
votes
1
answer
7k
views
Cannot alter MYSQL Table (stuck in a invalid default value loop)
Some how I have 2 columns with incorrect default values.
Not sure how it got created in the first place as I imported the db from another source.
Problem now is, I cannot make any changes to the ...
- 173
7
votes
5
answers
16k
views
Is it bad practice to add columns to an existing table?
Conversely: Is it better to get all the columns created at the time when a new table is created?
I'm working on a new system and new requirements are coming up all the time. The latest requirement ...
- 623