Stack Overflow
Stack Overflow

Questions tagged [alter-table]

The "ALTER TABLE" is a SQL statement that allows you to make schema changes to a database table (i.e. add, drop or alter columns from an existing table).

1118 questions
55
votes
5 answers

SQLite Modify Column

I need to modify a column in a SQLite database but I have to do it programatically due to the database already being in production. From my research I have found that in order to do this I must do the following. Create a new table with new…
Nathan
  • 5,059
  • 16
  • 48
  • 61
52
votes
1 answer

Renaming multiple columns in one statement with PostgreSQL

Is it possible to rename multiple columns in a single statement, something along the lines of: ALTER TABLE Users RENAME COLUMN userName TO user_name, RENAME COLUMN realName TO real_name;
Rovanion
  • 4,382
  • 3
  • 29
  • 49
49
votes
11 answers

How to add a column in TSQL after a specific column?

I have a table: MyTable ID FieldA FieldB I want to alter the table and add a column so it looks like: MyTable ID NewField FieldA FieldB In MySQL I would so a: ALTER TABLE MyTable ADD COLUMN NewField int NULL AFTER…
Justin808
  • 20,859
  • 46
  • 160
  • 265
48
votes
6 answers

Problem with Postgres ALTER TABLE

I have one problem with the ALTER TABLE in postgre. I want to change size of the varchar column. When I try to do this, It says that the view is dependent on that column. I can't drop the view because comething else is dependent on it. Is there any…
martin.malek
  • 2,166
  • 2
  • 19
  • 31
46
votes
3 answers

Does adding a null column to a postgres table cause a lock?

I think I read somewhere that running an ALTER TABLE foo ADD COLUMN baz text on a postgres database will not cause a read or write lock. Setting a default value causes locking, but allowing a null default prevents a lock. I can't find this in the…
jpadvo
  • 6,031
  • 3
  • 26
  • 30
40
votes
6 answers

Alter table to modify default value of column

I have a requirement where we need to modify a column's default value in database table. The table is already an existing table in database and currently the default value of the column is NULL. Now if add a new default value to this column, If I am…
ravi
  • 1,707
  • 4
  • 29
  • 44
39
votes
3 answers

Add primary key to PostgreSQL table only if it does not exist

I have simple table creating script in Postgres 9.1. I need it to create the table with 2-attributes PK only if it does not exist. CREATE TABLE IF NOT EXISTS "mail_app_recipients" ( "id_draft" Integer NOT NULL, "id_person" Integer NOT…
Pavel S.
  • 11,892
  • 18
  • 75
  • 113
38
votes
2 answers

MySQL: ALTER IGNORE TABLE ADD UNIQUE, what will be truncated?

I have a table with 4 columns: ID, type, owner, description. ID is AUTO_INCREMENT PRIMARY KEY and now I want to: ALTER IGNORE TABLE `my_table` ADD UNIQUE (`type`, `owner`); Of course I have few records with type = 'Apple' and owner = 'Apple…
kuba
  • 3,670
  • 3
  • 31
  • 41
38
votes
2 answers

How to add a default value to an already existing column?

I have an existing column in my SQL Server database. I have tried about everything I can think of but can not get a default value to be added to the column. What works in every other database is alter table mytable alter column mycolumn set…
Earlz
  • 62,085
  • 98
  • 303
  • 499
37
votes
4 answers

Add a column if it doesn't exist to all tables?

I'm using SQL Server 2005/2008. I need to add a column to a table if it does not yet exist. This will apply to all tables in a given database. I hoped I was close, but I'm having issues with this solution. How can this be done? Here's what I…
Scott Stafford
  • 43,764
  • 28
  • 129
  • 177
37
votes
5 answers

Rename column only if exists

PostgreSQL does not allow ALTER TABLE t RENAME COLUMN IF EXISTS c1 TO c2 ...or anything like that. However, it's very convenient to be able to write scripts which modify DB structure which can be run again without first checking if it has already…
NessBird
  • 745
  • 1
  • 6
  • 15
37
votes
1 answer

How to alter "REFERENCES" in PostgreSQL?

How can I alter the reference to a table in PostgreSQL when the table name has been changed? Say I have: CREATE TABLE example1 ( id serial NOT NULL PRIMARY KEY, name varchar(100) ); CREATE TABLE example2 ( id serial NOT NULL PRIMARY KEY, …
Jose Luis de la Rosa
  • 696
  • 1
  • 10
  • 14
37
votes
4 answers

Hive Alter table change Column Name

I am trying to rename a columnName in Hive. Is there a way to rename column name in Hive . tableA (column1 ,_c1,_c2) to tableA(column1,column2,column3) ??
user2978621
  • 803
  • 2
  • 11
  • 20
36
votes
1 answer

What is the difference between "ADD KEY" and "ADD INDEX" in MySQL?

I dump database and i have one question. What does this query? ALTER TABLE `ps_cart_rule` ADD KEY `id_customer` (`id_customer`,`active`,`date_to`); ALTER TABLE `ps_cart_rule` ADD KEY `id_customer_2`…
Bejkrools
  • 1,129
  • 2
  • 17
  • 38
36
votes
3 answers

ALTER TABLE add constraint

The tables User and Properties were created properly CREATE TABLE Properties ( ID int AUTO_INCREMENT, language int, stonecolor int, gamefield int, UserID int, PRIMARY KEY(ID), FOREIGN KEY(language) REFERENCES…
ABLX
  • 716
  • 2
  • 7
  • 18
1 2 3
…
74 75