Questions tagged [alter-table]

SQL statement used to alter an existing table object.

Filter by
Sorted by
Tagged with
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. (...
Dex's user avatar
  • 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 ...
user avatar
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 ...
alok's user avatar
  • 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 ...
gerardw's user avatar
  • 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 ...
mankowitz's user avatar
  • 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. ...
Granny Aching's user avatar
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: ...
Isaac Moses's user avatar
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 ...
SeanGaff's user avatar
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 ...
Quade's user avatar
  • 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), ...
Bryce's user avatar
  • 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 ...
jarviliam's user avatar
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 ...
Shayan's user avatar
  • 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 ...
Brijesh Roy's user avatar
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 ...
gkatzioura's user avatar
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 ...
lyeaf's user avatar
  • 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 ...
Padraic Renaghan's user avatar
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 ...
Federico Baù's user avatar
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 ...
p.matsinopoulos's user avatar
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 ...
Pradyut Bhattacharya's user avatar
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 ...
Clyde Barrow's user avatar
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 ...
Retrosec6's user avatar
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 ...
Mohd Waseem's user avatar
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 ...
sdds's user avatar
  • 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 (...
Ora_en's user avatar
  • 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 ...
hadi pourneshati's user avatar
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 ...
Dave's user avatar
  • 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 ...
Newbie-DBA's user avatar
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. ...
GG.'s user avatar
  • 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" ...
ogirginc's user avatar
  • 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 ...
Jason Crothers's user avatar
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 ...
Citizen1138x's user avatar
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 ...
vesperto's user avatar
  • 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, `...
ankit.vishen's user avatar
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 ...
Rajan Sharma's user avatar
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 ...
Avi's user avatar
  • 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 &...
Tran Triet's user avatar
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 `...
showkey's user avatar
  • 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:...
Life after Guest's user avatar
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 ...
Ali's user avatar
  • 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" ...
GergelyPolonkai's user avatar
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 ...
Ali's user avatar
  • 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 ...
SarangK's user avatar
  • 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 ...
nosilver4u's user avatar
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 ...
TheGremlyn's user avatar
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)) | +-----...
Titiksha 's user avatar
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 (...
Joe's user avatar
  • 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 ...
mikelus's user avatar
  • 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 ...
LaVache's user avatar
  • 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 @...
amrinder's user avatar
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, ...
Mark Amery's user avatar
  • 1,075

1
2 3 4 5
8