Questions tagged [alter-table]
SQL statement used to alter an existing table object.
384 questions
2
votes
2
answers
59
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
147
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
69
views
Add RowVersion column to a very large table
We need to add rowversion column to a very large table. We are on SQL 2022 and came across this post where Paul White mentioned about trace flag - 4085 - How can I add a rowversion column to a large ...
- 39
0
votes
1
answer
41
views
MariaDB: Can't update large table after ALTER
I have a database that I have imported from mysql dump, it's around 1GB and 4.3 million rows. (It is the compact dump from libgen from here https://data.library.bz/dbdumps/). After I import this table ...
- 109
1
vote
1
answer
89
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
98
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 ...
- 16.6k
0
votes
1
answer
275
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 ...
- 103
0
votes
0
answers
82
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
31
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
75
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 ...
- 384
0
votes
0
answers
37
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 ...
- 533
0
votes
1
answer
71
views
performance - Alter table from NVARCHAR(MAX) to NVARHCHAR(4000) - HADR_THROTTLE_LOG_RATE_MISMATCHE -
I'm Altering some tables with a lot of NVARCHAR(MAX) column to NVARCHAR(4000) in Azure sql environment.
I've already upscaled the database to 3000 DTU, and executed the script to alter.
I see that ...
- 47
1
vote
2
answers
605
views
Alter columnt type from int to bigint with minimal downtime
I have several tables with id columns that are declared to be serial (i.e. integer type with default nextval()).
Converting them to bigint seem to be quite straightforward:
ALTER TABLE my_table
ALTER ...
- 129
0
votes
1
answer
94
views
How do I protected my sensitive data (financial) from website/CRM programmers?
As a company owner, I need to protect my sensitive data from my website/CRM programmers. I know I can scramble DB values with a simple script but it needs to be applied manually all the time and when ...
- 123
4
votes
2
answers
171
views
Table size increased after changing datatype from nchar to varchar
I am facing a strange behavior when changing datatype from nchar(100) to varchar(100) for one column.
I understand if I change datatype of a column where data is present then it can increase size but ...
- 113
1
vote
0
answers
175
views
Altering the column without sysadmin or db_owner roles in published tables
I am using replication between two MS SQL Servers (2017 14.0.3421.10).
Unfortunately, when replication publishing is switched on, my user for database schema changes is not able to ALTER any columns. (...
- 111
0
votes
1
answer
108
views
Why does adding a new UniqueIdentifier column to my existing SQL Server table increases data space of the table by 100%?
I have an existing table and once I add an ObjectId UniqueIdentifier column with default NewSequentialId() value, the data space suddenly doubles. I'm using the script below:
ALTER TABLE Employee
ADD ...
Shackle Shot
0
votes
1
answer
136
views
postgresql RLS enablement : enable if not enabled check - worth it?
In our application, we check at startup,
if a postgresql table exists
if it doesn't, we create it, and then enable row level security
If it does exist, we still aren't certain if row level security is ...
- 251
0
votes
1
answer
525
views
Convert PostgreSQL array type
Given an enum type (e.g. letter).
What is the syntax to convert an existing array to the enum type? The existing values are valid for the enum. For example, how would I convert letter_data in:
create ...
- 103
0
votes
2
answers
201
views
Changing int column to bigint on table with blob column
I have an aws hosted RDS production table called external_documents as defined below. It has about 35k rows and each blob is about 0.5 MB.
CREATE TABLE `external_documents` (
`id` bigint unsigned ...
- 156
0
votes
1
answer
109
views
How do I add a primary key to a huge table without a primary key
I have a huge table (millions of records) that keeps history of events.
Table is automatically populated, with newer records added all the time. The field timestamp refers to when a row was added. ...
- 383
0
votes
1
answer
2k
views
Postgresql: Even when I grant the owner role, I can't alter tables
I have a Postgres database with a schema named foo owned by a user also named foo, which also owns all of the tables in it.
While logged in as foo, I created a user bar and granted it everything foo:
...
- 113
3
votes
1
answer
842
views
Oracle database: how to change varchar length of same column in multiple tables in one go?
My company uses Oracle 19 and we have many thousands of tables which have two columns called CRBY and MODBY in them, which are varchar2(200). These columns get populated by a script called on an ...
- 37
2
votes
1
answer
2k
views
Converting varchar to decimal with truncate
I have a column called latitude which is currently varchar(20)
I want to convert it to DECIMAL(9,6)
However the data stored inside the column is greater than 6 decimal points
i.e.
48.123456891123456
...
- 301
1
vote
1
answer
1k
views
Does adding "NOT NULL DEFAULT X" Column to Postgres cause a table re-write?
Does adding a column with NOT NULL DEFAULT X lock the table and cause a table re-write in Postgres 14?
If it doesn't lock the table and cause a table-rewrite (assuming it's just a metadata change), ...
- 35
1
vote
1
answer
504
views
Would a row-level lock affect a DDL ALTER TABLE statement on Postgres?
Postgres version: 13.8
A week or so ago we ran a migration to safely drop a column on one of our tables. This column is not referenced by any other table and there was no index on the column. As far ...
- 13
1
vote
0
answers
993
views
Convert Integer Array to JSON Array
I'm not sure how I should go about converting int[] to json[], here's what I've tried so far:
db=# alter table product alter column images type json[] using images::json[];
ERROR: cannot cast type ...
- 111
0
votes
1
answer
1k
views
Alter command showing syntax error in MySQL 8.0
Below is my Table schema
create table employee(
emp_id numeric(4),
fname varchar(10),
lname varchar(10),
mgr_id numeric(4),
hire_date date,
job_id numeric(2),
dept_id numeric(3)
);
Now I want to use ...
- 19
3
votes
1
answer
3k
views
Add composite primary key on existing PostgreSQL table
I have a table on PostgresSQL with millions of rows. The table has columns a and b. I already have a unique index on a and b:
CONSTRAINT a_b UNIQUE (a, b)
I want to add a composite primary key on ...
- 133
6
votes
1
answer
649
views
Is it possible to estimate how much time is left for a DROP statement?
I made this query : ALTER TABLE dbname.tablename DROP COLUMN columnname and it's been running for a few hours and I wanted to know how much time would it still be going.
There are approximatively 750m ...
- 197
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 ...
4
votes
1
answer
2k
views
MySQL | Set Column default with value of an existing database that is a SELECT result
Problem
Having an exsiting database, a we need to add a column to a table, this column being of type int, is there a way to set the default initial value a result of a query in the database?
Just to ...
- 151
0
votes
1
answer
616
views
Can I have an indication of ETA for an ALTER statement in PostgreSQL?
PostgreSQL 14 here.
I am currently running an ALTER command on a huge table. It has already been running for 13 hours and it keeps going. It is active. Is there a way I could get an indication of when ...
- 109
3
votes
2
answers
2k
views
mysql takes too long to just add a column
MySQL took 4.5 minutes to just add a column to a table having 150,000 rows.
Is there anything wrong in it or this is just normal. Is there any other alternative to fasten things?
ALTER TABLE job_posts
...
1
vote
1
answer
2k
views
Mysql - changing autoincrement type from unsigned int to bigint - How long could it take?
I have a table that has a primary key like id int(11) unsigned auto_increment primary key. The table has 100 mln rows and I estimated its size to 28GB.
I'd like to change primary key type to unsinged ...
- 113
1
vote
1
answer
1k
views
Tables character-set migration without huge downtime- mysql
We're in the process of migrating to a new character set for several tables in our database. Some tables are currently using utf8/utf8mb3 character set, which is deprecated now and we want move on ...
- 13
7
votes
1
answer
3k
views
Change Column Data Type from numeric(18, 2) to numeric(22, 6) on a BIG Table
We have a very big POSTGRES table containing more than 8 BILLION rows and growing at a very high rate (30 million rows per day).
Our Table is Partitioned on date. Each partition contains 6 months data ...
- 171
1
vote
0
answers
272
views
PostgreSQL — considerations when adding new values to rows with a long-running migration
I have a table of ten columns containing 370 million rows and I need to add six new columns to it. Some new columns will take null values, others will be defined with NOT NULL DEFAULT. After altering ...
- 111
0
votes
0
answers
554
views
Object necessary for warmstarting database cannot be altered ORACLE
I have this error when I want to rebuild system index :
00701. 00000 - "object necessary for warmstarting database cannot be altered"
*Cause: Attempt to alter or drop a database object (...
- 1
0
votes
1
answer
352
views
can use maxdop on online modifying column in mssql 2019?
I want to change data type of a column of my table in SQL Server 2019.
My table has 50,000,000 row and it's very important.
I want to do this operation online and also handle maxdop to avoid CPU over ...
0
votes
0
answers
38
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 ...
- 173
2
votes
0
answers
77
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 ...
- 794
4
votes
2
answers
6k
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.
...
- 145
6
votes
1
answer
18k
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" ...
- 265
2
votes
3
answers
8k
views
Adding a column to a table if column not exists and fill the added column
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 ...
1
vote
0
answers
149
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 ...
- 19
0
votes
1
answer
1k
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 ...
- 125
0
votes
1
answer
1k
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,
`...
- 115
0
votes
0
answers
245
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 ...
0
votes
0
answers
101
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 ...
- 265