Questions tagged [alter-table]
SQL statement used to alter an existing table object.
369
questions
1
vote
0
answers
28
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
44
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
15
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 ...
- 249
0
votes
1
answer
31
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
32
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
45
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. ...
- 385
0
votes
1
answer
53
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
138
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
622
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
...
- 171
1
vote
1
answer
295
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
186
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
399
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
408
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
2
votes
1
answer
468
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 ...
- 123
6
votes
1
answer
498
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 ...
- 165
5
votes
1
answer
1k
views
ALTER timestamptz column to timestamp 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
560
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 ...
- 145
0
votes
1
answer
169
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 ...
- 107
3
votes
2
answers
801
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
773
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
0
votes
1
answer
418
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 ...
6
votes
1
answer
1k
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 ...
- 161
0
votes
0
answers
150
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 ...
- 101
0
votes
0
answers
301
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
183
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
31
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
46
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 ...
- 762
2
votes
1
answer
4k
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.
...
- 125
6
votes
1
answer
11k
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
1k
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
119
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
688
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,
`...
- 113
0
votes
0
answers
191
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
56
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
2
votes
2
answers
2k
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 &...
- 123
0
votes
1
answer
1k
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 `...
- 264
1
vote
1
answer
61
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:...
- 113
0
votes
1
answer
445
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 ...
- 195
0
votes
0
answers
293
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" ...
- 101
1
vote
1
answer
2k
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 ...
- 195
0
votes
0
answers
561
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 ...
- 109
1
vote
1
answer
1k
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 ...
- 61
0
votes
1
answer
295
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
...
- 101
1
vote
2
answers
542
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)) |
+-----...
- 13
1
vote
1
answer
809
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 (...
- 179
0
votes
1
answer
268
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 ...
- 323
5
votes
1
answer
2k
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 ...
- 303
0
votes
2
answers
333
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 @...
- 1
8
votes
1
answer
7k
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, ...
- 1,075