Questions tagged [alter-table]

SQL statement used to alter an existing table object.

Filter by
Sorted by
Tagged with
0 votes
0 answers
17 views

Oracle: why does this DDL yield a different result in production (reordering of columns)?

Problem description This week we had an issue in production, where an SQL script that was executed ended up with a different result than what we had tested before locally, in Docker and on our ...
user avatar
  • 173
2 votes
0 answers
33 views

How to change data type of PK for very min to no maintenance while performing this action

We had issue on our prod server for one of the tables approx 500 M rows and one of the column int has maxed out its length limit. So probable suggestion from dev team was to change data type of that ...
user avatar
0 votes
1 answer
37 views

ALTER TABLE … ADD COLUMN on a small table takes forever, pg_stat_activity doesn't show any queries on this table

So this is the query that hangs forever: ALTER TABLE tasks ADD COLUMN in_progress BOOLEAN NOT NULL DEFAULT FALSE; The table tasks has less than 20,000 rows and is queried once every 5 minutes or so. ...
user avatar
  • 105
3 votes
1 answer
264 views

Is it safe to use default value with not null when adding a new column?

We have a Rails app powered by Postgresql v11.4 where I want to add a new column with a default value and a not null constraint like below: ALTER TABLE "blogs" ADD "published" ...
user avatar
  • 235
1 vote
1 answer
28 views

Adding a column to a table then updating the table

I have a code that looks something like this declare @query = ' Alter Table Temp Add NewColumn int update Temp set NewColumn = 100' exec (@query) It will give error NewColumn does not ...
user avatar
1 vote
0 answers
19 views

Data in one or more tables corrupt after recovering tables with alter table discard/import tablespace

I accidentally dropped my schema and had to recover all the tables per the steps in this post; Recover schema and the data in it from accidentally deleted schema However, I notice that all of the ...
user avatar
0 votes
1 answer
66 views

What is the standard SQL way to rename a column?

So far I've seen ALTER TABLE RENAME, ALTER TABLE CHANGE and ALTER TABLE MODIFY. What I'm trying to achieve is something like ALTER TABLE my_table RENAME col_old_name TO col_new_name, which can be a ...
user avatar
  • 125
0 votes
1 answer
79 views

Mysql Duplicate entry error during new column addition on production database

I have a MySQL table that has 95929357 records, below is the table schema for the same CREATE TABLE `Friends` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `createdTime` datetime DEFAULT NULL, `...
user avatar
0 votes
0 answers
47 views

Change Column size of a table

We have a mysql 5.6 cluster, 1 master 5 slaves. Requirements:- We have to modify the size of a column varchar from 20 to 35 for 2 databases. What is the best approach for doing this accross the ...
user avatar
0 votes
0 answers
27 views

Adding new column to clustered column store indexed table

I designing clustered column store indexed fact tables. Later on many new columns might be added to these currently ~90 columns tables. How adding new nullable column is affected by the size (number ...
user avatar
  • 266
1 vote
2 answers
314 views

Postgresql explain alter table add column doesn't work

I'm using Postgresql 12 (AWS RDS) and pgAdmin4. While the following statement works explain select * from table "Commands"; The next one explain alter table "Commands" drop column &...
user avatar
0 votes
1 answer
268 views

Data too long for column when to alter column definition

The column definition is as below: `code` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL I want to alter another table(option)'s code field as same definition: ALTER TABLE `...
user avatar
  • 126
1 vote
1 answer
27 views

Altering mysql database column causes error in another column

First of all sorry everybody if this question sounds too basic. I have a Wordpress database and a table wp_comments. In this table, I have two DATETIME fields, whose default value is 0000-00-00 00:00:...
user avatar
0 votes
1 answer
88 views

Adding Virtual Column after column x is very slow

In our environment, we usually add and drop generated columns every now and then. There is a very large table in the database with more than 3 million rows and adding/removing column was extremely ...
user avatar
  • 195
0 votes
0 answers
62 views

Creating a foreign key on an empty MySQL InnoDB table takes a very long time

I have created a table called asset_versions: CREATE TABLE "asset_versions" ( "asset_id" int NOT NULL, "version" varchar(50) NOT NULL, "created_at" ...
user avatar
1 vote
1 answer
205 views

Alter Table Drop Column INPLACE NoLock - until which point is the column being dropped accessible?

I am trying to run MySQL Alter INPLACE command to drop few columns from a very large 90GB table. While the Alter is running I am able to run the Select statement on the same table to ensure that the ...
user avatar
  • 195
0 votes
0 answers
75 views

Add new column and do conditional incremental insert in PostgreSQL

I am new to Postgres, I have a table where I need to add a new column and have to insert incremental values with some filter or you can say condition. My existing table- and I want to update it to ...
user avatar
  • 109
1 vote
1 answer
230 views

Why can't I use ALTER to set the default on a column?

Long story short, I have a table with a TIMESTAMP column where I want to make the default CURRENT_TIMESTAMP (default currently is 1971-01-01 00:00:00). This is part of a WordPress plugin, and so ...
user avatar
0 votes
1 answer
94 views

ALTER TABLE statements not replicating in all tested scenarios - MariaDB

Summary Tech: mariadb 10.3 Issue: some attempts to ALTER TABLE to change engine is not replicating. Scenarios tested: command line PHP script running ALTER statements using PDO - did not replicate ...
user avatar
1 vote
2 answers
153 views

How to rename a column name which has been created by a math function in mysql? Using ALTER TABLE is giving ERROR: 1064 (42000)

INPUT: CREATE TABLE dist -> SELECT ST_DISTANCE(POINT(x1,y1),POINT(x2,y2)) -> FROM config; OUTPUT: +------------------------------------------+ | ST_DISTANCE(POINT(x1,y1) , POINT(x2,y2)) | +-----...
user avatar
1 vote
1 answer
142 views

Alter table generated text column to normal text column

If I have a table with a generated text column, how do I alter the column to remove the generation expression to get a normal, not-generated text type? Here's the schema: CREATE TABLE product_history (...
user avatar
  • 149
0 votes
0 answers
88 views

Update table column without losing data when releasing a new version of software

I just linked my application with a database using sqlite. Everything worked fine. And I realeased a version of my app. So I find myself in need of changing a table attribute type. The sql statement I ...
user avatar
  • 101
0 votes
1 answer
75 views

Alter a table column based on type

I am converting "timestamps without timezone" columns to "timestamps with timezone" on a number of tables. These are large tables and I am going to use batching and other ...
user avatar
  • 293
2 votes
1 answer
694 views

Convert a postgres GENERATED column to a regular non-generated column (in place without DROP)

I'm using postgres 13.3, and I already have an existing table with a GENERATED column such as: CREATE TABLE test_table ( id uuid NOT NULL DEFAULT uuid_generate_v4(), the_column_to_alter bool ...
user avatar
  • 223
0 votes
2 answers
78 views

Changing nvarchar (4000) to nvarchar(500)

I have a performance issue with nvarchar(4000). I want to alter the table as per the statement below: BEGIN declare @length as int SELECT @length = MAX(LEN([SourceId])) from [dbo].[table] if @...
user avatar
1 vote
1 answer
1k 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, ...
user avatar
2 votes
2 answers
165 views

Convert postgres varchar[] to text[] without downtime

As I discovered in researching this question, converting a column from varchar(100)[] to text[] requires a table rewrite and rebuilding indexes. Is there a way to manage this conversion without the ...
user avatar
  • 243
3 votes
1 answer
293 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 ...
user avatar
  • 243
0 votes
3 answers
147 views

Supplying root password to MySQL V8 sort of fails

On Ubuntu 20.04 I have installed MySQL V8.0.25. But I kind of fail to apply a (valid) root password. The apt-get process for installing mysql-server did not ask for a root password. And I cannot enter ...
user avatar
  • 101
0 votes
1 answer
112 views

How to alter column to make it primary key when one already exists mysql8?

id | bigint unsigned | NO | PRI | NULL | auto_increment | | title | varchar(255) | NO | | NULL | | | category | varchar(255) | NO | | NULL |...
user avatar
0 votes
2 answers
198 views

Why is MySQL InnoDB table copied despite using ALGORITHM=INPLACE for ALTER TABLE

I run an ALTER STATEMENT on an InnoDB table on a MySQL server version 5.7 that: drop some foreign key constraints drop some indexes (non unique) add some columns (without index or foreign key ...
user avatar
  • 103
1 vote
1 answer
45 views

Significant performance differences executing alter and grant statements between users with same permissions

I am experiencing significant performances differences between users with the same permissions when executing alter and grant statements. I have an automated build system that runs a series of SQL ...
user avatar
  • 111
1 vote
5 answers
256 views

Why can't you add an Identity to an existing colum in SQL Server?

What is so special about an Identity column? I am pretty sure there is a valid reason, but I'm trying to get a better understanding of SQL. So what is so special about an Identity column that this can'...
user avatar
  • 333
1 vote
1 answer
54 views

Suppressing SELECT output before \gexec?

This answer is great for when I needed to reown 150 tables, each with 40 tables, but displays the 6000 rows from SELECT before running the 6000 ALTER statements. Thus, as stated in the Subject, is ...
user avatar
  • 475
0 votes
1 answer
51 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: ...
user avatar
  • 121
1 vote
1 answer
488 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 ...
user avatar
0 votes
1 answer
198 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, ...
user avatar
  • 1,411
1 vote
2 answers
2k 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 ...
user avatar
  • 135
0 votes
2 answers
255 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 "...
user avatar
  • 255
1 vote
1 answer
20 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 ...
user avatar
5 votes
1 answer
218 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 ...
user avatar
  • 341
0 votes
2 answers
347 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 ...
user avatar
  • 103
0 votes
1 answer
264 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 ...
user avatar
  • 107
0 votes
1 answer
135 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: ...
user avatar
1 vote
2 answers
72 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 ...
user avatar
0 votes
1 answer
68 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 ...
user avatar
0 votes
1 answer
115 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'; ...
user avatar
  • 56.2k
0 votes
1 answer
37 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'...
user avatar
0 votes
1 answer
618 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] ...
user avatar
1 vote
1 answer
1k 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 ...
user avatar

1
2 3 4 5
7