Questions tagged [alter-table]
SQL statement used to alter an existing table object.
389 questions
0
votes
1
answer
30
views
Changing MySQL NOT NULL column to allow NULL
Feeling like I'm doing something stupid here, but cannot figure out what it is. I'm trying to change an existing NOT NULL column to allow NULL values but it doesn't seem to be working. I'm using ...
- 780
8
votes
1
answer
521
views
Alter table alter column does not work with user defined types
A user with db_datareader, db_datawriter, db_ddladmin and db_securityadmin roles can add columns with a user defined type, but cannot alter those.
For example: Lets say we create a user defined type ...
- 83
4
votes
1
answer
164
views
"The data type of a column of a partitioned table can't be changed."
The documentation claims
The data type of a column of a partitioned table can't be changed.
It repeats this claim elsewhere such as here.
Yet I have never seen this fail.
CREATE PARTITION FUNCTION ...
- 1,488
1
vote
0
answers
101
views
ALTER TABLE ... REORGANIZE PARTITION on huge table takes longer after upgrading to MySQL 8.0
After upgrading from MySQL 5.7 to MySQL 8.0.33, I’ve observed a dramatic slowdown in partition management operations, particularly:
ALTER TABLE ... REORGANIZE PARTITION
ALTER TABLE ... DROP PARTITION
...
- 11
0
votes
1
answer
120
views
Phenomenon over add column to alter table with 300M records
In the context of an existing question that I had asked Performant way to perform update over 300M records in MYSQL. I assumed that the alteration of table schema would possible be trivial for the ...
- 123
2
votes
2
answers
1k
views
Do you need NOT VALID when adding new foreign key column
I know that adding a foreign key constraint requires a table scan and a SHARE ROW EXCLUSIVE lock on both tables.
To prevent the possibly lengthy table scan the constraint can be added with the NOT ...
2
votes
1
answer
796
views
Unable to alter CDC tables
I have an issue with being unable to alter a CDC table that previously caused us no issue.
We run alter statements on our cdc tables to add a column to the tables that we now are starting to face an ...
- 23
0
votes
1
answer
189
views
Add RowVersion column to a very large table
We need to add rowversion column to a very large table. We are on SQL Server 2022 and came across this post where Paul White mentioned about trace flag - 4085 - How can I add a rowversion column to a ...
- 39
1
vote
1
answer
246
views
Alter timestamptz column to timestamp, without re-writing the table
I'm using Postgres Debezium source connectors, which does not support columns with timezone. I want to convert a table column from type from timestamptz to timestamp.
Postgres table stores data in UTC ...
- 113
0
votes
3
answers
211
views
Alter table - add a new column as the first column in the table - sql server
Before doing a restore of a database I check how much space I have in each drive on the destination server.
I use this script:
IF OBJECT_ID ('tempdb..#FREE_SPACE_DRIVES','u') IS NOT NULL
BEGIN
DROP ...
- 17.3k
1
vote
1
answer
1k
views
How can I allow a user to alter tables they do not own in Postgres?
I need to be able to assign a role to users to allow them to do anything they want to tables in a schema. I need to do this without assigning superuser priv in Postgres.
I already figured out how to ...
- 113
0
votes
0
answers
126
views
Reduce varchar column size in a production database
We have a database running on AWS Aurora Mysql with version 8.0.mysql_aurora.3.04.1 as a regional database.
There is a table in it with a column with varchar size 200. We want to reduce it to varchar(...
0
votes
0
answers
109
views
Why trying to enable NULL values in a MySQL timestamp field fails?
I imported the table delete_me from the following mysqldump's exported file
-- MySQL dump 10.13 Distrib 5.7.42, for Linux (x86_64)
--
-- Host: 192.168.00.00 Database: main
-- ----------------------...
- 145
0
votes
1
answer
142
views
Alter all tables in schema to set an existing field as an IDENTITY PRIMARY KEY
Is there an easy and robust way to execute these two commands on all tables in a given schema myschema in a PostgreSQL (13) database:
ALTER TABLE myschema.table001 ADD PRIMARY KEY (oid);
ALTER TABLE ...
- 444
0
votes
0
answers
73
views
Getting OOM when trying to change id from int to bigint in MySQL w/ percona
We have 2.5tb table with id column as int in Aurora 3 (MySQL 8.0.26).
We need to change it to bigint as ids will run out soon.
We tested it with pt-online-schema-change and the server crashed because ...
- 529