Questions tagged [alter-table]
SQL statement used to alter an existing table object.
295
questions
0
votes
0answers
16 views
Altering big table with gh-ost on Google Cloud
We need to add some indexes and one additional column to a large MySQL table.
MySQL is Google Cloud SQL version 5.7.25-google-log.
Some table info:
currently holding 1 331 363 322 rows
size of the ...
0
votes
3answers
41 views
How to calculate end_date from start_date and duration in SQL Server Query
I have a table with 4 fields .Start-date, Duration, End_date, Actual_date.
I am providing the start_date and duration as the input to calculate end-date.
While calculating the end_date the ...
0
votes
0answers
24 views
Check constraint on grouped conditions
Take the following table as an example:
GroupID AlphaID BetaID
1 0 1
1 1 1
1 2 2
2 0 1
2 1 2
...
0
votes
1answer
31 views
How to fix conversion error from InnoDB to MyISAM?
How can I fix conversion error from InnoDB to MyISAM for these tables?
MariaDB [db_name]> ALTER TABLE wp_posts ENGINE = MyISAM;
ERROR 1071 (42000): Specified key was too long; max key length is ...
0
votes
1answer
19 views
Atler Table Alter Column Set Default FALSE: Why my alter table took 10 minutes to finish?
I have run the following command and it took about 10 minutes to finish:
ALTER TABLE "bookings" ALTER COLUMN "local_direct_booking" SET DEFAULT FALSE
for a table bookings which had 111.834 rows (...
0
votes
1answer
41 views
MySql Error #1054 when adding new column to table using prepared statement
I'm trying to add a new column named lot to a table, but I'm getting error #1054 - Unknown column 'lot' in 'field list'. What could be the problem? Here's the query:
SET @lot = (SELECT lot_no_start ...
0
votes
0answers
21 views
Adding a column and new foreign key constraint in one line
Let's say we have a table called Salesman with the folling properties
Create Table SalesMan(
EmplyeeId int Primary Key,
Name varchar(20) Not Null,
LastName Varchar(20) Not Null,
Email Varchar(...
0
votes
0answers
25 views
Alter Table operations blocks replication in MySQL
We are using Amazon Web Service's MySQL 5.7.26 with 4 read-only replicas. After some Alter Table operations we have found that the replication stops working. The symtoms are great replica lag that is ...
1
vote
1answer
41 views
Does “alter table … set data type” functionally differ from “alter table … type”?
The docs show [SET DATA] TYPE which implies the SET DATA part is optional, but the 9.1 release notes also imply that SET DATA TYPE may be different:
Allow ALTER TABLE ... SET DATA TYPE to avoid ...
2
votes
1answer
122 views
ALTER fixed-length COLUMN on compressed table
I wonder if compression of a table actually changes ALTER TABLE rules. I found no information on this topic. Let's consider a simple example. I'll create a table and alter one of the columns from INT ...
0
votes
0answers
21 views
ALTER TABLE ADD [duplicate]
I have a development SQL Server (SQL 2017) and a production one (SQL 2019).
When I run the following statement on dev it is pretty much instantaneous
ALTER TABLE dbo.BatchStaging ADD
...
0
votes
1answer
39 views
Alter on huge table consumes all disk space
I'm running MySQL 8 on Ubuntu 19. I have a table that is 543G and I have to add a new column and index. I've started with the new column:
alter table hugeTable add column newCol tinyint(1) after ...
0
votes
0answers
22 views
ALTER TABLE query to convert table to utf8mb4_bin takes many hours - how to optimise this?
I have a table which has approx 15 million rows - I won't add the schema here but it has 96 columns (far too many in my opinion).
The current table collation is latin1_swedish_ci but for full emoji ...
4
votes
1answer
90 views
Prevent drop column with index on it in PostgreSQL
In PostgreSQL when you drop column in a table, all indexes and constraints which use this column will be also dropped silently. If you overlook such situation, you can get serious problems in ...
0
votes
0answers
50 views
Dynamic script to alter a column type having an index
I am writing a script to update the datatype of a column in SQL Server on a list of tables, if the existing column are not yet of the desired type. The column should be converted from a VARCHAR to a ...
0
votes
0answers
73 views
How to rename parent table and child tables in partition tables postgres 9.6
I am trying to rename parent table in partitions. I created 45 child tables on monthly wise manner. I can alter the name of parent table but I don't know how to alter the 45 child table references.
...
1
vote
1answer
139 views
Drop multiple columns at the same time from a SQL Server table
I am currently looking some way to delete multiple columns at the same time from a given table in sql server.
One way of doing that is to use ALTER statement as shown below:
Alter Table [USER_DB].[...
1
vote
1answer
44 views
SQL Server - Migration script testing - Refresh table definition manually without dropping it
How can a table definition be manually refreshed without also manually crafting a bunch of ALTER TABLE statements, or dropping and recreating the table?
This is normally handled by the publish ...
12
votes
4answers
412 views
Fast alter column NVARCHAR(4000) to NVARCHAR(260)
I have a performance problem with very large memory grants handling this table with a couple of NVARCHAR(4000) columns. Thing is these columns are never larger than NVARCHAR(260).
Using
ALTER TABLE [...
-1
votes
1answer
18 views
what happen you using put the ALTER statement in a transaction.?
I got a question about
"What happens when you using Alter table command when a database is running?
is it possible to run the alter table when database is running?
and if it's possible what will ...
1
vote
1answer
83 views
Alter table add column with no default blocks
tl;dr
The following query blocks the postgres server when executed
ALTER TABLE "image_data" ADD COLUMN "user_status" varchar(30) NULL
engine version 10.6
# of rows 1.5 mils
Hi,
I have a somewhat ...
2
votes
0answers
31 views
What is the order of execution of alter operations in an alter query in MySQL?
Consider the following queries
CREATE TABLE MyTable (f2 INT);
ALTER TABLE MyTable ADD COLUMN f1 INT, DROP COLUMN f1;
In the above query drop column seems to happen before add column and query fails....
0
votes
1answer
723 views
Entity Framework 6: How can I add new column to existing table and at the same time update view (add column) in same migration file [closed]
Example in the EF6 migration file Up method.
I wish to add a new column B in existing table Country.
Sql("ALTER TABLE [dbo].[Country]
ADD [B] INT DEFAULT((0)) NOT NULL;")
Then I will need to ...
0
votes
1answer
20 views
Altering Tables using Tablenames from other Table
I have a database with several tables and i have a table which contains all tablenames which I want to alter.
I want to perform a query like this:
ALTER TABLE (SELECT "TABLE_NAME" FROM "...
2
votes
2answers
320 views
Db2 REORGCHK returns the SQL2310N (Error “-668”) message
I have a Db2 database on my Linux laptop. The database is not heavily loaded. I access it from time to time for the development purposes.
I removed a column:
db2 "alter table SCHEMA.TABLE drop ...
0
votes
0answers
98 views
Possibility of using self-referential foreign key to create hierarchical comments
I am an accidental DBA and have a question regarding the table structure of a website I maintain (postgresql 9.6 backend).
Imagine a typical content website (let's call it 'Links'), where users post ...
11
votes
1answer
1k views
Is ALTER TABLE … DROP COLUMN really a metadata only operation?
I've found several sources that state ALTER TABLE ... DROP COLUMN is a meta-data only operation.
Source
How can this be? Does the data during a DROP COLUMN not need to be purged from the underlying ...
2
votes
1answer
84 views
Change column datatype without rewrite when data remains unchanged
For example:
timestamp(tz) to int8
date to int4
json to text
The raw data is unchanged, and the only difference is how that data is handled. So it should be possible to change back and forth cleanly ...
3
votes
1answer
2k views
SQLServer add a default value for a column [closed]
I am using MS SQL Server. I have an "action" table with an "action_type" column that I want to set a default value of "to do" for. My DDL in MS SQL Studio errors! I have read many many articles trying ...
12
votes
3answers
725 views
Increase speed altering column on large table to NON NULL
I recently added a NULL-able bit column to a table which has close to 500 million rows. There's not a default on the column, however all inserts are specifying a value of 0 or 1, and I ran a one-time ...
1
vote
1answer
22 views
Do the action blocks of an ALTER TABLE statement run atomically?
In Postgres, ALTER TABLE can accept multiple action forms, separated by a comma.
For example:
ALTER TABLE mytable
DROP CONSTRAINT pkey,
ADD CONSTRAINT pkey PRIMARY KEY USING INDEX myindex;
...
1
vote
1answer
30 views
MySQL replication: how to safely propagate ignored table structure changes from master to slave?
We have a MySQL database with a replication set up (master to single slave).
Some tables are skipped from replication (they are listed in Replicate_Wild_Ignore_Table when calling SHOW SLAVE STATUS). ...
0
votes
1answer
25 views
How to change all columns from one type to another, changing all FK columns as well?
In an inherited DB we found that there are around 25 tables with a primary key defined as tinyint.
For a few of those, it's an imminent problem, since this optimistic optimization clashes with the ...
1
vote
3answers
2k views
How do I move all tables from one Postgres schema to another
I want to move all tables from schema xyz to the default schema public.
I can move individual tables with
ALTER TABLE table_name
SET SCHEMA public;
And I can get all tables with
SELECT ...
0
votes
1answer
43 views
How to add a constraint to a table using a partial index?
I have a simple table:
create table item (
lan text not null,
disabled date
);
My aim is to ensure uniqueness for the lan column over all rows, where disabled is null.
I created a partial ...
0
votes
1answer
136 views
Alter table - existing column as primary key
I am attempting to set one of the columns in the table as primary key.
Here are the columns from the table:
Now, when I run the sql:
ALTER TABLE cb_company
ADD PRIMARY KEY(uuid);
I get the ...
0
votes
1answer
19 views
During table migration of tables from one tablespace to another, does it needs free space to do the alter table move?
During migration of tables from one tablespace to another (all datafiles in the same disk/partition/mount), does it needs free space to do the alter table move?
exemple:. I have 30gb of free space on ...
0
votes
2answers
53 views
Moving tables to new tablespace needs temp space or any kind of addictional space? (Oracle 11g)
I've been trying to search for this info on oracle documents but they just don't talk about it.
I need to move about 2TB of different tables from 3 different tablespaces to a new tablespace (...
1
vote
1answer
438 views
Convert an int column to bigint with the 4 bytes of padding available
If there's 4 bytes of padding that would fit the bigint perfectly, is it possible to efficiently convert it on-the-spot by taking advantage of that gap?
CREATE TABLE a (
b SERIAL PRIMARY KEY,
...
0
votes
1answer
2k views
Error parsing DDL for schema.table in mysql workbench
I have production dbs using Mysql 5.5.31-0ubuntu0.12.04-1 (I know its old, will be upgraded soon).
I used a too advanced version of mysql workbench which allowed me to add a comment to key. (Now ...
0
votes
1answer
312 views
Error Code: 1845. ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE
I upgraded to MySQL 8.0.13 and tried to add a new field using ALGORITHM=INSTANT but got the following error:
Error Code: 1845. ALGORITHM=INSTANT is not supported for this
operation. Try ALGORITHM=...
0
votes
0answers
311 views
What makes ALTER TABLE X SET UNLOGGED; to be slow?
We develop and maintain a JEE Application with Postgres (multiple version of it as we've got different customers using different versions). The Code evolves over time; database changes are organized ...
1
vote
1answer
3k views
Syntax error when trying to add columns to table in DBeaver (postgresql)
I've tried this a few different ways but every time I get a syntax error when trying to add columns to a table.
I'm new to databases so it's probably something basic that I'm doing wrong, but I just ...
1
vote
2answers
989 views
Converting Varchar to Int error
I came across an issue today, and while I was able to fix it, I am unsure what caused the issue to happen.
I have a column that is Varchar(20). It is used to store just year data (2010, 2011, etc.) ...
0
votes
2answers
63 views
Time taken to drop/add index is lesser than adding a column. Why is that?
I'm using 10.1.25-MariaDB-1~xenial, but engine is innodb though.
I'm performing an alter by default it does ONLINE alter.
When I add column it takes at least 50 mins for 50G table in our ...
2
votes
0answers
84 views
How costly is changing column datatype in PostgreSQL?
I'm currently evaluating PostgreSQL for an app we are developing which comes with a weird usecase. We want to allow clients to create new fields which in turn will create new columns in a client-...
3
votes
0answers
1k views
change column type from integer to varchar
I have an integer column size in my table product in PostgreSQL 10.5 database. I want to change it's type from int to varchar(20) using alter table if possible.
The obvious way which worked for me ...
0
votes
1answer
52 views
why when I change a column from DATETIME to DATE, I can't see any storage improvement?
Long story short. I have a table with more than 10millions rows with a datetime field. WE can change that to DATE ( because date uses 3bytes, and datetime 8).
I thought if I:
alter table table1 ...
2
votes
1answer
680 views
Converting float4 to numeric in PostgreSQL is rounding the values
I have a table that has a column storing some coefficients which require high precision. The type of the column is set to float4 and I need to change it to numeric.
But after altering the table, it ...
0
votes
1answer
59 views
Performance: ALTER TABLE with default value vs. iterating over all records to set new value [closed]
For adding values to a new column, I have the following options:
Using default to add value during ALTER TABLE command.
Doing ALTER TABLE without default, then fetching all the records and iterating ...
