Skip to main content

Questions tagged [alter-table]

SQL statement used to alter an existing table object.

Filter by
Sorted by
Tagged with
-1 votes
0 answers
14 views

Summing values from different tables in access

I have 3 tables, student table stores information about students, Fee table stores information on fees paid by students and Collections table stores information on what a student has paid for which is ...
Zephaniah Nyamweya's user avatar
0 votes
0 answers
38 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(...
Tanmay Sule's user avatar
0 votes
0 answers
25 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 -- ----------------------...
0xC0DEGURU's user avatar
0 votes
1 answer
27 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 ...
s.k's user avatar
  • 372
0 votes
0 answers
24 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 ...
Nir's user avatar
  • 533
0 votes
1 answer
42 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 ...
dexon's user avatar
  • 47
1 vote
1 answer
234 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 ...
Eduard Sukharev's user avatar
0 votes
1 answer
86 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 ...
abenci's user avatar
  • 113
0 votes
0 answers
40 views

IBM Db2 - How to monitor progres of ALTER TABLE command?

On IBM Db2 v11.5.8.0 Linux/x86_64 I have to add several columns in pretty big table with ALTER TABLE command. On test database now it takes for more then an hour, but I don't have any info how long ...
folow's user avatar
  • 347
4 votes
2 answers
154 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 ...
saadkaul's user avatar
  • 113
1 vote
0 answers
86 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
80 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
86 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
  • 251
0 votes
1 answer
254 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
125 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
76 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
1k 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
550 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
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 ...
Quade's user avatar
  • 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), ...
Bryce's user avatar
  • 35
1 vote
1 answer
424 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
757 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
883 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
3 votes
1 answer
2k 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
609 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
  • 177
5 votes
1 answer
3k 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
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 ...
Federico Baù's user avatar
0 votes
1 answer
397 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
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 ...
Pradyut Bhattacharya's user avatar
1 vote
1 answer
1k 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
898 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
7 votes
1 answer
2k 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
1 vote
0 answers
218 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
  • 111
0 votes
0 answers
452 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
280 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
33 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
66 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
3 votes
1 answer
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. ...
GG.'s user avatar
  • 135
6 votes
1 answer
15k 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
6k 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
144 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
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 ...
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
237 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
75 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
2k 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
  • 334
1 vote
1 answer
145 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
682 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
  • 305
0 votes
0 answers
402 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
2 3 4 5
8