Questions tagged [alter-table]

SQL statement used to alter an existing table object.

Filter by
Sorted by
Tagged with
4
votes
3answers
5k views

Will an ALTER TABLE ADD COLUMN on a slave break replication?

I need to add a last_modified column to a MyISAM table tbl_items. The crux of this problem is that tbl_items houses several gigabytes of data. Also of note, I am using a master-slave deployment with ...
1
vote
2answers
5k views

How can one drop all default values and allow NULL for all columns of a table?

I'm working on a PHP script to modify the primary key (PK) of a table. My current code is: $q = "SHOW KEYS FROM $db.$table WHERE Key_name='PRIMARY';"; $keys = Query ( $q); if ( $keys) { $q = "...
1
vote
1answer
379 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, ...
3
votes
1answer
20k views

H2 ALTER TABLE ADD COLUMN BEFORE/AFTER

According to H2 syntax documentation available here I should be able to execute a query like: ALTER TABLE EMPLOYEE ADD COLUMN SECOND_NAME VARCHAR (124) AFTER FIRST_NAME; However apparently only this ...
5
votes
3answers
2k views

Migrating from NTEXT to NVARCHAR(MAX)

I have table with the columns of type NTEXT. And for some peculiar reasons, the client wants to migrate the column type to NVARCHAR(MAX). While doing so, what all potential threat should I be careful ...
0
votes
1answer
1k 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
278 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 ...
5
votes
5answers
40k views

How To change the column order?

How To change the column order in sql server 2008 R2? Eg: My table order is Eid------Ename-----Esalary Know i want this format like Eid---Salary----Ename Thanks.
1
vote
1answer
2k 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
847 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.) ...
3
votes
1answer
3k views

More Best Practices To Updating Large Tables?

I know there are a number of topics on this question, but I'm always seeking more insights. I have a large table with a billion+ records. The amount of records could be reduced and archived, but the ...
2
votes
2answers
2k views

Altering CHAR(n BYTE) Column

How to change CHAR (17 byte) to CHAR (16 byte) when there is already a data? Values are something like this: 012345-000-00001 There is a space at the end. I tried to trim it first before altering ...
0
votes
2answers
62 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 ...
1
vote
1answer
684 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.
4
votes
6answers
5k views

Modifying table structure within a transaction?

In Oracle (and probably elsewhere), executing an ALTER TABLE statement will do an implicit commit on the current transaction. We have a tool (written in Java) that should modify a schema by: adding ...
12
votes
2answers
10k views

Can I rename the values in a MySQL ENUM column in one query?

Suppose I have a Database table with an ENUM('value_one','value_two'). I want to change that to an ENUM('First value','Second value'). I an currently doing that as follows: ALTER TABLE `table` MODIFY ...
2
votes
0answers
62 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-...
0
votes
1answer
12k views

Adding a default constraint in Oracle while altering a column

I'm new to Oracle. I need to port a SQL Server command to Oracle. I want to alter a column to add a new constraint with a default value. SQL Server command ALTER TABLE <schema_name>.<...
12
votes
2answers
4k views

Add NOT NULL constraint to large table without table scan [duplicate]

Trying to add a NOT NULL constraint to a table with 1 billion rows. I cannot afford a table lock for more than a couple of seconds. Is there a way to prevent a full table scan during the alter table ...
16
votes
3answers
78k views

Reset IDENTITY value

I have a table with an IDENTITY column. While developing I delete the rows from time to time and add them again. But the IDENTITY values always kept increasing and didn't start from 1 when I added ...
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 ...
11
votes
2answers
14k views

Quickly change NULL column to NOT NULL

I have a table with millions of rows and a column that allows NULL values. However no row currently has a NULL value for that column (I can verify this fairly quickly with a query). However when I ...
2
votes
1answer
580 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
53 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 ...
26
votes
6answers
46k views

Alter table on live production databases

How do most "popular" (MySQL, Postgres...) database system handle altering tables on live production databases (like adding, deleting or changing the type of colums)? I know the correct way is to ...
1
vote
4answers
1k views

MySQL - Alter Table Fails As Workbench Tries To Create Existing Table

I have been trying to add another column to a table in my database using MySQL Workbench. This generates an 'ALTER TABLE' command which then fails as it fails attempting a 'CREATE TABLE, which it ...
9
votes
2answers
6k views

Does PostgreSQL optimize adding columns with non-NULL DEFAULTs?

When adding NOT NULL columns with a DEFAULT value - does PostgreSQL optimize this operation? In case the table has n rows, a not-optimized alter-table-add-column would yield n writes of the default ...
1
vote
2answers
2k 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
323 views

Altering a parent table in Postgresql 8.4 breaks child table defaults

The problem: In Postgresql, if table temp_person_two inherits fromtemp_person, default column values on the child table are ignored if the parent table is altered. How to replicate: First, create ...
1
vote
1answer
118 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
2answers
2k views

How move MySql table to another disk?

According to this it is impossible to use alter table for moving tables. So if I understand correctlly - to move table it is neccessary to do: Create new table Copy all row from old one drop old ...
1
vote
1answer
220 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 ...
1
vote
1answer
56 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 ...
6
votes
2answers
5k 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
1answer
872 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
149 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.
2
votes
1answer
883 views

How to apply a newly-created Domain to an existing Column?

In Postgres 9.4 Beta 1, I successfully created a domain of type VARCHAR, fuel_domain_. The domain checks that a value is any of 5 possible strings, coal, gas, wind, hydro, other. CREATE DOMAIN ...
4
votes
1answer
89 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, ...
1
vote
1answer
933 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 ...
18
votes
3answers
12k views

Why would I ever prefer ALGORITHM=COPY to ALGORITHM=INPLACE?

Since MySQL 5.6 introduced online DDL, the ALTER TABLE command can optionally have either ALGORITHM=INPLACE or ALGORITHM=COPY specified. The overview of online DDL notes that, by default, INPLACE is ...
2
votes
3answers
2k 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 ...
4
votes
2answers
6k views

PostgreSQL - how to rename CamelCase column?

When I tried to rename a CamelCase column like this: ALTER TABLE mytable RENAME COLUMN camelCaseColumn TO camel_case_column; I get the following error: ERROR: column "camelcasecolumn" does not ...
6
votes
1answer
4k 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 ...
6
votes
1answer
731 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 ...
31
votes
4answers
28k views

I need to run VACUUM FULL with no available disk space

I have one table that is taking up close to 90% of hd space on our server. I have decided to drop a few columns to free up space. But I need to return the space to the OS. The problem, though, is ...
5
votes
2answers
2k 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 ...
14
votes
3answers
14k views

ALTER TABLE on a large table with an indexed column

I have a large table with a VARCHAR(20) column, and I need to modify that to become a VARCHAR(50) column. Typically, performing an ALTER TABLE (adding a TINYINT) on this particular table takes about ...
1
vote
3answers
1k 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
37 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 ...