Questions tagged [alter-table]
SQL statement used to alter an existing table object.
0
votes
1answer
10 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
0answers
11 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
33 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 ...
0
votes
1answer
12 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
44 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
31 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
34 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-...
1
vote
0answers
98 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
49 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
57 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
34 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 ...
1
vote
1answer
66 views
Query Performance When Dropping Multiple Columns
In PostgreSQL, is there any performance gain when using a single ALTER TABLE statement to drop multiple columns, instead of using a separate statement to drop each column?
ALTER TABLE table DROP ...
1
vote
1answer
55 views
How do I an add a DEFAULT on a column if the table already exists?
I want to add a DEFAULT for a timestamp column in PostgreSQL. With SQL Server you use ALTER TABLE ADD CONSTRAINT
You can see the example in their docs,
ALTER TABLE dbo.doc_exz
ADD CONSTRAINT ...
0
votes
0answers
34 views
How much size of log file may create if we alter one column of table having size of 136 GB?
We have one table in production environment having 870 million records.The size of table is around 136 GB.We are altering (i.e increasing its precision) one decimal column in the same table.We have ...
1
vote
1answer
33 views
Convert Character Encoding, and Check Encoding for Issues
I'm planning to convert a table from utf8 (_general_ci) to utf8mb4 (utf8mb4_unicode_ci). I want to make sure I don't have any malformed data after the conversion though so I was planning to duplicate ...
5
votes
2answers
720 views
Does ALTER TABLE - ADD COLUMN lock the table?
I have a question about the ALTER TABLE ... ADD COLUMN DDL statement.
On a Amazon RDS instance with MariaDB v10.2, I've noticed that INSERT statements complete and the rows are correctly inserted in ...
0
votes
2answers
95 views
Add column with default in postgresql without table level lock
Have such a problem - table with over 20mln rows.
When i add new column with default - postgresql lock table for over 40 minutes so my application stop working for this time.
So instead of
ALTER ...
0
votes
1answer
267 views
Rename column in a partitioned table
I want to rename a column in a partitioned table.
Will oracle do this for all partitions?
Is there anything special for this change?
0
votes
1answer
27 views
How to replace foreign reference in PostgreSQL? [closed]
Suppose I have a foreign reference column which is pointing to an item A, how do I change it to item B? I would like to modify all records whose foreign reference column is A to B.
4
votes
1answer
58 views
Does MySQL allows incoming queries while altering a table?
I'm adding a new column to an existing table say ABC with the following query,
ALTER TABLE ABC add column STATUS int(10) NOT NULL
This is done in master, slave and secondary slaves. Meanwhile, ...
0
votes
0answers
35 views
I have a model of a table, and would like to keep database in sync, what is best method?
The model is an ordered (using additional array) hash table, with keys being columns' names, and values being columns' types. To visualize in an abstract programming language:
MyHashTable[2] = ( "...
0
votes
1answer
241 views
Oracle 11g Fast Add Column Taking 11 Hours
To the best of my understanding, a new feature of Oracle 11g is the ability to add a not null column with a default value very quickly as it would no longer auto-update every row in the table with the ...
2
votes
3answers
634 views
How long can this ALTER TABLE statement take? [closed]
Alter TABLE [XXX] Alter column [YYY] [varchar](max) NULL
Suppose
there are 45 GB data space and 2GB index space;
about 3 million rows in this table;
column YYY is varchar(8000) now and can be ...
6
votes
1answer
284 views
How to add a PostgreSQL 10 identity column to an existing table with rows?
I have a table with some rows and I try adding an identity column using the command:
ALTER TABLE sourceTable
ADD COLUMN ogc_fid int
GENERATED BY DEFAULT AS IDENTITY;
The ...
4
votes
2answers
730 views
Will altering an nvarchar(max) column to nvarchar(50) lock the table?
I have a table in SQL Server (Azure) with 1.4 million rows.
I'd like to index one of the columns; however, its datatype is nvarchar(max) (defaulted to such by using EF I imagine). I'm using Entity ...
4
votes
1answer
1k views
How to add a PostgreSQL 10 identity column to an existing table?
I'm reading about the new Postgres 10 identity column, and saw how it is used to replace SERIAL columns when CREATE TABLE.
Is it also possible to add an identity column to an existing table?
For ...
1
vote
3answers
520 views
Alter table force fully
I'm trying to decrease the precision of a column from NUMBER(25) to NUMBER(10) but when I do I got error saying column to be modified must be empty to decrease precision
I do not have any data in the ...
0
votes
1answer
26 views
Why the owner of a database can't alter the tables of this database that are owned by other owners?
For now I don't understand why this access rights segregation exist.
Is it implemented for some security reasons? Or can it be needed by some use cases I can't imagine right now?
Knowing almost ...
-2
votes
3answers
147 views
Disadvantages of converting column with 2 billion data from varchar to int [closed]
I want to convert a column from varchar(50) to bigint or int.
What are the disadvantages when tables has 2 billion data in SQL Server 2012?
Length of data is 11 and data has only numbers. They were ...
0
votes
1answer
35 views
H2 Database - Alter multiple databases
I need to alter a table in H2 db. But we have millions of databases with the same same table. What may be the feasible way to alter such databases?
1
vote
1answer
38 views
How to restrict column creation for existing tables?
I have a table already created in a schema which has columns a, b, and c. I want to restrict the user(s) from creating new columns.
How to do this?
I have gone through the manual pages about GRANT ...
0
votes
1answer
123 views
Alter table with casting
Is there a way of run an alter table command with cast on MySQL databases?
I know that is possible to do it with Postgresql and Sql servers, but I didn't find anything about it when searching the ...
1
vote
1answer
659 views
How can I change TIMESTAMP to TIMESTAMP(3)?
I have a column of TIMESTAMP, NOT NULL, DEFAULT CURRENT_TIMESTAMP.
I want to modify it as TIMESTAMP(3).
I tried
ALTER TABLE <table>
MODIFY COLUMN <column>
TIMESTAMP(3) NOT NULL DEFAULT ...
0
votes
1answer
109 views
DB2 Alter column type and index state RBDP vs. “needs a rebuild”
I'm new to DB2 and databases overall.
I'm building a script to alter several column data types in several tables of which some are part of indexes and some not. So after ALTER-statements I assumed I ...
0
votes
1answer
265 views
How to Alter and add a suffix “DO_NOT_DROP” to several tables using PL/SQL LOOP
Say I have table A(see below) which has details of all the tables that has to be renamed. Also, what if the table does not exist and how to handle exception and find out how many of the tables were ...
2
votes
1answer
496 views
Date of last change of an InnoDB table structure
I was wondering how to determine when was the last change (ALTER TABLE...) done to a given table.
An old answer is available but unfortunately that doesn't work for InnoDB tables.
Would this work, ...
1
vote
1answer
395 views
Problem with aborted PT-online-schema change command
I aborted a pt-online-schema change command to change a table definition. Now, when I run pt-online-schema change again, I get this error:
The table <db name>.<table name> has triggers. ...
2
votes
1answer
256 views
ALTER TABLE with missing FROM-clause entry for table “inventory”
ALTER TABLE inventory.subtitles
ALTER COLUMN id SET DEFAULT (inventory.subtitle_subtitle_id_seq);
produces missing FROM-clause entry for table "inventory"
but even pgadmin 3 gives me the same ...
1
vote
2answers
887 views
Nvarchar column getting truncated to 256 characters
I have a SQL Server Express 2012 database. There is a table which previously had a column of type nvarchar(255). Now my requirements have changed and I am required to store long strings in the same ...
1
vote
1answer
2k views
Does innodb_online_alter_log_max_size took memory from ram or Hard Disk
I got an Mysql (5.7) error while altering huge table (400 G) for adding an index:
ERROR 1799 (HY000): Creating index 'FTS_DOC_ID_INDEX' required more than 'innodb_online_alter_log_max_size' bytes of ...
1
vote
0answers
249 views
How to find MariaDb/Mysql structure differences
I have two similar databases with many tables, in one of these there was some alter commands maybe changing data types or adding some columns.
Exists a free analyzer/merging tool to check their ...
0
votes
1answer
54 views
Is using innodb_file_per_table improve speed of alter table command?
As innodb_file_per_table option separates table files instead of putting all data and indexes of DBs into one ibdata file, is using this option improve speed of alter table?
I have a table of 40M ...
3
votes
2answers
4k views
Alter large table without downtime
I have a very large table, lets call it example, and I try to execute a little alter command on that table:
ALTER TABLE `example` ADD `example_column` TINYINT(1) NOT NULL DEFAULT 0;
Checking the ...
0
votes
1answer
261 views
Amazon Postgres RDS: Update pg_attribute to update my custom attribute
I want to update my attribute to change its length. I am getting
Error: Permission denied for relation pg_attribute.
My SQL Query is like this:
DO $$
BEGIN
UPDATE
pg_attribute
SET
...
1
vote
1answer
219 views
How do I ALTER all columns of one type to another type?
Let's say I want to change the data type of all columns of one type to another. How would I go about doing this, for example converting all bigints to ints.
1
vote
1answer
202 views
Nothing changed; always get connection timeout on alter table in MariaDB
I have a MariaDB 10.1.21 database running in Fedora 25. I successfully ran alter table statements on auto_incrementcolumns in the past. After updating MariaDB to 10.1.21, but without changing any ...
0
votes
2answers
3k views
alter primary id field to uniqueidentifier GUID as default in sql server
I have tables with id(primary key - int) with identity specification (1,1) .
I want to change this column to uniqueidentifier with default ( newid() ) .
What I've tried is
ALTER TABLE myTable ALTER ...
0
votes
1answer
35 views
alter table which is under the load (sql server)
we have table that being used for sending sms and other notification for confirmation as second factor.
Some changes in business logic requires us to alter table to make some columns NOT NULL.
Our ...
0
votes
1answer
244 views
Has the ID column configured as PK, NN and AI, be the first column on my table definition?
I have a .csv file with a bunch of data which I am using for load all the data into a DB table. The file lacks the ID column (because that's is an internal column managed through our software and they ...
0
votes
2answers
1k views
UPDATE Column values with Case and JOIN
I have 2 tables "a" and "b".
Table "a"
| property | element | ply | ..
Table "b"
| property | ply | material | ..
What I want to do is to add a column to the first table called "material" and ...
