Questions tagged [alter-table]
SQL statement used to alter an existing table object.
314
questions
0
votes
1answer
23 views
Improving remake of primary key performance
I'm using a MySQL 5.7 hosted on AWS that has a table with about 500,000,000 rows and 124GB in size.
I want to drop the existing primary key (id) and create a new composite primary key of 2 columns:
...
1
vote
1answer
27 views
Why do my RDS read IOPS drop after a certain period of time?
I am trying to add non-null constraints on several columns in a table with about 70,000,000 rows. Each statement is just ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL;, and each ...
0
votes
1answer
26 views
Change bucket count for Primary Key Nonclustered Hash on existing In-Memory table
I'm on SQL Server 2019 Enterprise edition. I have a table with this definition
CREATE TABLE dbo.MyMemoryTable
(
FirstId int NOT NULL,
SecondId int NOT NULL,
MyCount int NOT NULL,
...
0
votes
0answers
14 views
Snowflake: Creating a table with a column name which is ANSI reserved fails, but adding a column(alter table) same ANSI reserved column name succeeds
CREATE TABLE test_table (LOCALTIMESTAMP varchar(10))
This query fails with .invalid column definition name 'LOCALTIMESTAMP' (ANSI reserved)
ALTER TABLE test_table ADD COLUMN LOCALTIMESTAMP varchar(...
1
vote
2answers
41 views
“Alter Table” modify multiple columns vs multiple Alter table single column in INNODB?
I am updating collations of my database tables and columns from utf8bin to utf8mb4_bin.
Engine is INNODB with file per table enabled.
I used a query to generate identify all utf8 columns from ...
0
votes
2answers
29 views
How to add generated column to db2 table?
I've got a table with the following:
Columns:
---
URL [nullable, e.g. "https://dba.stackexchange.com/"]
APPFK [nullable, e.g. 654654]
LINKTYPE ["Page", "Link", or "...
1
vote
1answer
16 views
Why am I getting an error if I am trying to modify data type? [closed]
I am facing an error when I passed the following syntax in MySQL:
ALTER TABLE store MODIFY (DOB char(40)) ;
I am attaching a screenshot too because then you can see that the column DOB is having all ...
5
votes
1answer
78 views
Why a dropped index is still there, and does it prevent a table from being altered?
I have a column (named RequestDate) which I need to change from NULL to NOT NULL, but there's a clustered index on that column, so I first need to drop that index.
Originally, the index was created ...
0
votes
2answers
30 views
In mysql, how to add a primary key and a composite key to a table so that it forbids duplicate rows, when there are null values within the columns?
In the following table (8 mi rows), every column has Null values. However, there can never be two identical rows (there will be always at least one column with a different value for each row).
How can ...
0
votes
1answer
31 views
How do you change the storage parameters of a already made table (sql developer)?
Summarize the problem
I have been given 6 tables that have all been created already and I was told to change the storage parameters of those tables. the issue I run into is that I can only change ...
0
votes
1answer
29 views
alter table alter column causes huge log and data file growth
I have a table with 2.6B rows in it and I need to change a column from Char(1) to Varchar(64). the table is over 300GB with a few indexes. The column in nullable, so the transaction i'm running is:
...
1
vote
2answers
32 views
Promote MySQL Table Partition to Table
My Google Searching skills were not enough to find a solution to this. If possible, how can I promote a partition on a table to a table on its own?
Let's assume I have part0, part1, and part2. Is ...
0
votes
1answer
37 views
SQL Server :: how to move country code column to another table?
I have 2 tables:
tbl_Countries: 250 values, with columns like CountryName (Afghanistan), TwoCharCountryCode (AF), ThreeCharCountryCode (AFG).
Like this:
CountryName TwoCharCountryCode ...
0
votes
1answer
35 views
Why is ALTER faster than UPDATE (for full table rewrites)?
Let's create a garbage table..
CREATE TABLE foo
AS SELECT x::text FROM generate_series(1,20e6) AS t(x);
Time: 14077.654 ms (00:14.078)
Now, let's look at boring UPDATE,
UPDATE foo SET x = 'foo';
...
0
votes
1answer
19 views
How to insert '1001-01-01 00:00:00' into MariaDB when altering a table?
I created a table in HeidiSQL:
CREATE TABLE `publish_dt` (
`ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
`TS` DATETIME NULL DEFAULT NULL,
`Text` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_unicode_ci'...
0
votes
1answer
106 views
Alter column fails due to “hidden” statistic
I'm trying to change a column data type from datetime2 to datetimeoffset. I have db owner permissions.
Whenever I run the query:
alter table dbo.<my-table>
alter column [TimeStamp] ...
1
vote
1answer
108 views
MySQL Store Procedure - Setting AUTO_INCREMENT value
I am in trouble coding a store procedure as shown below
CREATE DEFINER=`dbcomexuser`@`%` PROCEDURE `clearance_imp`(IN CAPIfilename VARCHAR(12))
BEGIN
DECLARE `_rollback` BOOL DEFAULT 0;
DECLARE ...
0
votes
1answer
124 views
Why is ADD CONSTRAINT … NOT VALID taking a long time?
I added NOT NULL constraints to some columns like this, expecting the command to be very fast because of NOT VALID. However, it took about 60-100 seconds, and seemed to lock the table for the whole ...
0
votes
1answer
32 views
Specific format/formula when changing column datatype [duplicate]
Can one modify a column's datatype using a very specific conversion format?
For example, when converting from datetime to varchar I'd like to use the yyyy-MM-dd hh:mm:ss format, not the system ...
0
votes
2answers
56 views
MySQL: change column type using expression
In a MySQL database, I need to convert some UUID columns (BINARY(16)) into VARCHAR. I want to convert them in the canonical form, not as bytes. For clarity, in lowercase and with hyphens, and not in ...
5
votes
2answers
291 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 ...
0
votes
0answers
33 views
Altering big table with gh-ost on Google Cloud
We need to add some indexes and one additional column to a large MySQL table.
MySQL is Google Cloud SQL version 5.7.25-google-log.
Some table info:
currently holding 1 331 363 322 rows
size of the ...
1
vote
3answers
115 views
How to calculate end_date from start_date and duration in SQL Server Query
I have a table with 4 fields .Start-date, Duration, End_date, Actual_date.
I am providing the start_date and duration as the input to calculate end-date.
While calculating the end_date the ...
0
votes
0answers
25 views
Check constraint on grouped conditions
Take the following table as an example:
GroupID AlphaID BetaID
1 0 1
1 1 1
1 2 2
2 0 1
2 1 2
...
0
votes
1answer
52 views
How to fix conversion error from InnoDB to MyISAM?
How can I fix conversion error from InnoDB to MyISAM for these tables?
MariaDB [db_name]> ALTER TABLE wp_posts ENGINE = MyISAM;
ERROR 1071 (42000): Specified key was too long; max key length is ...
0
votes
1answer
21 views
Atler Table Alter Column Set Default FALSE: Why my alter table took 10 minutes to finish?
I have run the following command and it took about 10 minutes to finish:
ALTER TABLE "bookings" ALTER COLUMN "local_direct_booking" SET DEFAULT FALSE
for a table bookings which had 111.834 rows (...
0
votes
1answer
91 views
MySql Error #1054 when adding new column to table using prepared statement
I'm trying to add a new column named lot to a table, but I'm getting error #1054 - Unknown column 'lot' in 'field list'. What could be the problem? Here's the query:
SET @lot = (SELECT lot_no_start ...
0
votes
0answers
24 views
Adding a column and new foreign key constraint in one line
Let's say we have a table called Salesman with the folling properties
Create Table SalesMan(
EmplyeeId int Primary Key,
Name varchar(20) Not Null,
LastName Varchar(20) Not Null,
Email Varchar(...
0
votes
0answers
29 views
Alter Table operations blocks replication in MySQL
We are using Amazon Web Service's MySQL 5.7.26 with 4 read-only replicas. After some Alter Table operations we have found that the replication stops working. The symtoms are great replica lag that is ...
1
vote
1answer
46 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 ...
2
votes
1answer
207 views
ALTER fixed-length COLUMN on compressed table
I wonder if compression of a table actually changes ALTER TABLE rules. I found no information on this topic. Let's consider a simple example. I'll create a table and alter one of the columns from INT ...
0
votes
1answer
61 views
Alter on huge table consumes all disk space
I'm running MySQL 8 on Ubuntu 19. I have a table that is 543G and I have to add a new column and index. I've started with the new column:
alter table hugeTable add column newCol tinyint(1) after ...
0
votes
0answers
25 views
ALTER TABLE query to convert table to utf8mb4_bin takes many hours - how to optimise this?
I have a table which has approx 15 million rows - I won't add the schema here but it has 96 columns (far too many in my opinion).
The current table collation is latin1_swedish_ci but for full emoji ...
4
votes
1answer
168 views
Prevent drop column with index on it in PostgreSQL
In PostgreSQL when you drop column in a table, all indexes and constraints which use this column will be also dropped silently. If you overlook such situation, you can get serious problems in ...
0
votes
0answers
185 views
Dynamic script to alter a column type having an index
I am writing a script to update the datatype of a column in SQL Server on a list of tables, if the existing column are not yet of the desired type. The column should be converted from a VARCHAR to a ...
1
vote
1answer
318 views
Drop multiple columns at the same time from a SQL Server table
I am currently looking some way to delete multiple columns at the same time from a given table in sql server.
One way of doing that is to use ALTER statement as shown below:
Alter Table [USER_DB].[...
1
vote
1answer
44 views
SQL Server - Migration script testing - Refresh table definition manually without dropping it
How can a table definition be manually refreshed without also manually crafting a bunch of ALTER TABLE statements, or dropping and recreating the table?
This is normally handled by the publish ...
13
votes
4answers
481 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 [...
-1
votes
1answer
18 views
what happen you using put the ALTER statement in a transaction.?
I got a question about
"What happens when you using Alter table command when a database is running?
is it possible to run the alter table when database is running?
and if it's possible what will ...
1
vote
1answer
256 views
Alter table add column with no default blocks
tl;dr
The following query blocks the postgres server when executed
ALTER TABLE "image_data" ADD COLUMN "user_status" varchar(30) NULL
engine version 10.6
# of rows 1.5 mils
Hi,
I have a somewhat ...
2
votes
0answers
33 views
What is the order of execution of alter operations in an alter query in MySQL?
Consider the following queries
CREATE TABLE MyTable (f2 INT);
ALTER TABLE MyTable ADD COLUMN f1 INT, DROP COLUMN f1;
In the above query drop column seems to happen before add column and query fails....
0
votes
1answer
1k views
Entity Framework 6: How can I add new column to existing table and at the same time update view (add column) in same migration file [closed]
Example in the EF6 migration file Up method.
I wish to add a new column B in existing table Country.
Sql("ALTER TABLE [dbo].[Country]
ADD [B] INT DEFAULT((0)) NOT NULL;")
Then I will need to ...
0
votes
1answer
21 views
Altering Tables using Tablenames from other Table
I have a database with several tables and i have a table which contains all tablenames which I want to alter.
I want to perform a query like this:
ALTER TABLE (SELECT "TABLE_NAME" FROM "...
2
votes
2answers
540 views
Db2 REORGCHK returns the SQL2310N (Error “-668”) message
I have a Db2 database on my Linux laptop. The database is not heavily loaded. I access it from time to time for the development purposes.
I removed a column:
db2 "alter table SCHEMA.TABLE drop ...
0
votes
0answers
171 views
Possibility of using self-referential foreign key to create hierarchical comments
I am an accidental DBA and have a question regarding the table structure of a website I maintain (postgresql 9.6 backend).
Imagine a typical content website (let's call it 'Links'), where users post ...
11
votes
1answer
2k 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 ...
2
votes
1answer
123 views
Change column datatype without rewrite when data remains unchanged
For example:
timestamp(tz) to int8
date to int4
json to text
The raw data is unchanged, and the only difference is how that data is handled. So it should be possible to change back and forth cleanly ...
3
votes
1answer
2k views
SQLServer add a default value for a column [closed]
I am using MS SQL Server. I have an "action" table with an "action_type" column that I want to set a default value of "to do" for. My DDL in MS SQL Studio errors! I have read many many articles trying ...
12
votes
3answers
953 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 ...
1
vote
1answer
23 views
Do the action blocks of an ALTER TABLE statement run atomically?
In Postgres, ALTER TABLE can accept multiple action forms, separated by a comma.
For example:
ALTER TABLE mytable
DROP CONSTRAINT pkey,
ADD CONSTRAINT pkey PRIMARY KEY USING INDEX myindex;
...
