Questions tagged [alter-table]
SQL statement used to alter an existing table object.
337
questions
0
votes
2answers
50 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 &...
0
votes
1answer
27 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 `...
1
vote
1answer
18 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:...
0
votes
1answer
27 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 ...
0
votes
0answers
23 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" ...
1
vote
1answer
41 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 ...
0
votes
0answers
36 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 ...
1
vote
1answer
15 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 ...
0
votes
1answer
20 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
...
1
vote
2answers
30 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)) |
+-----...
0
votes
1answer
21 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 (...
0
votes
0answers
42 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 ...
0
votes
1answer
38 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 ...
1
vote
1answer
156 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 ...
0
votes
2answers
53 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 @...
0
votes
1answer
114 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, ...
2
votes
2answers
56 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 ...
3
votes
1answer
45 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 ...
0
votes
3answers
79 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 ...
0
votes
1answer
26 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 |...
0
votes
2answers
66 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 ...
1
vote
1answer
39 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 ...
1
vote
5answers
127 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'...
1
vote
1answer
29 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 ...
0
votes
1answer
37 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
232 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
85 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
88 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
771 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
111 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
18 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
99 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
111 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
124 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
45 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
46 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
42 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
66 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
25 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
390 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
539 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
692 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
247 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
1k 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 ...
2
votes
3answers
351 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
26 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
105 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
23 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
175 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 ...
