Questions tagged [alter-table]
SQL statement used to alter an existing table object.
45 questions
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
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
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
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
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
5
votes
1
answer
4k
views
ALTER timestamp column to timestamptz, without "converting" data?
It seems that altering a column from timestamp without time zone to timestamp with time zone converts the existing data based on the current session time zone at the time of the alter statement.
See ...
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
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
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
6
votes
3
answers
30k
views
Change column type from varchar to text in all tables at once
I am using Postgres. I would like to change column type in all tables where column name is "description" from varchar(255) to text.
If anyone knows that I would be very glad for your help.
- 163
4
votes
1
answer
5k
views
Why dropping all foreign keys from a script is not working? [closed]
I have sysadmin rights on a SQL Server 2008 R2 box. I found a script that can drop all foreign keys and re-add them for us when we need to after a testing import.
So I execute the below code with no ...
- 655
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
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
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
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
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
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
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
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 ...
4
votes
2
answers
17k
views
Preordering the table by a specified column
I have a table and I am always getting the data by ordering a column. In my case, I order by datenum. However, the selection is so slow because of the ordering.
Is it possible to order the table ...
- 1,017
2
votes
1
answer
747
views
Table size is not changed after new column is added
I am trying to check how the table size is going to be increased after adding new column with default values.
I use the following default values: NULL, ''(empty string) and some text(some random text)...
- 4,318
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
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
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
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
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
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
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 ...
5
votes
1
answer
5k
views
How to set column DEFAULT using value of a variable
In a script for setting up a database which uses random UUIDs for primary keys, I have:
CREATE TABLE alpha (id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), value INT);
CREATE TABLE beta (aref UUID ...
- 153
5
votes
4
answers
13k
views
Alter Table for Multiple Databases (same table name)
Is there a good way to Alter a table (add a column) for multiple databases (think like 50 or so). Or is it pretty much manual for all of them?
the Tables are all the same and the column added will be ...
user1031
4
votes
3
answers
2k
views
guideline for calling optimize table after alter table?
Should I be calling 'optimize table' when changing datatypes of columns, adding indexes or dropping indexes, assuming a table with millions of rows, heavily used, both for reading and writing (InnoDB)....
- 473
3
votes
1
answer
6k
views
How to run ALTER TABLE on a list of tables sequentially in a function
I have a schema (called import) where data are imported as tables, and a function that processes them all. After that I want to move these tables away in another schema (called data_archive). The ...
- 191
3
votes
2
answers
29k
views
How to make a trigger that will compare input with a value of other table?
I had made two tables with the following fields:
TABLE 1: "Bookings"
FIELD: "fk_flight_number"
FIELD: "date_of_reservation"
TABLE 2: "Flights"
FIELD: "flight_number"
FIELD: "...
3
votes
1
answer
2k
views
Postgres varchar(100)[] -> text[] column conversion
I need to change a column type from varchar(100)[] to text[]. The table has >100M rows and heavy write load, so I'd like to avoid taking an access exclusive lock and rewriting the index. I saw in ...
- 305
3
votes
3
answers
3k
views
Changing a column's data type fills the transaction log
Running this code:
ALTER TABLE npidata
ALTER COLUMN npi varchar(20)
Gives this error:
Msg 9002, Level 17, State 4, Line 2
The transaction log for database 'SalesDWH' is full. To find out why ...
- 255
3
votes
1
answer
473
views
Does "alter table … set data type" functionally differ from "alter table … type"?
The docs show [SET DATA] TYPE which implies the SET DATA part is optional, but the 9.1 release notes also imply that SET DATA TYPE may be different:
Allow ALTER TABLE ... SET DATA TYPE to avoid ...
- 133
1
vote
2
answers
7k
views
2013. Lost connection to MySQL server during query error
I have a table with close to 20 million rows.
In phpMyAdmin, I attempt to add a new column:
ALTER TABLE `myTable` ADD `newColumn` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '0';
After about 60 secs (...
- 23
1
vote
4
answers
2k
views
MySQL - Alter Table Fails As Workbench Tries To Create Existing Table
I have been trying to add another column to a table in my database using MySQL Workbench. This generates an 'ALTER TABLE' command which then fails as it fails attempting a 'CREATE TABLE, which it ...
- 449
1
vote
1
answer
2k
views
How do I ALTER all columns of one type to another type?
Let's say I want to change the data type of all columns of one type to another. How would I go about doing this, for example converting all bigints to ints.
- 64.7k
1
vote
1
answer
2k
views
How do I an add a DEFAULT on a column if the table already exists?
I want to add a DEFAULT for a timestamp column in PostgreSQL. With SQL Server you use ALTER TABLE ADD CONSTRAINT
You can see the example in their docs,
ALTER TABLE dbo.doc_exz
ADD CONSTRAINT ...
- 21
1
vote
3
answers
183
views
Amount of data in a database table
I am trying to run an alter query on a table, and its giving me a
Lost connection to MySQL server during query
MySQL server has gone away.
The query that I am running is
alter table table_name ...
sweetu514
1
vote
1
answer
2k
views
trigger or check constraint insert values based upon existence of others
For simplicity (and since it captures the essence of my problem), suppose I have a table with data.
CREATE TABLE my_practice_table
(
my_id NUMBER(12),
field_2 VARCHAR2(20),
field_3 ...
-1
votes
2
answers
10k
views
Oracle: ORA-00904: "xxxx": invalid identifier when doing "Alter Table xxxx Modify ..." [closed]
I'm trying to sync a table on test system to one in production.
In production, as bad as it may be, I believe they used the reserved word SITE as a column name.
alter table MY_TABLENAME
modify ("...
- 577