YouTipPostgresql Alter Table
## PostgreSQL ALTER TABLE Statement
In PostgreSQL, the `ALTER TABLE` statement is used to modify the structure of an existing table. This command allows you to add, modify, or drop columns. Additionally, you can use `ALTER TABLE` to add and remove various constraints on a table.
---
## Syntax
Below are the common syntaxes used with the `ALTER TABLE` statement in PostgreSQL.
### 1. Add a Column
To add a new column to an existing table:
```sql
ALTER TABLE table_name ADD column_name datatype;
```
### 2. Drop a Column
To delete an existing column from a table:
```sql
ALTER TABLE table_name DROP COLUMN column_name;
```
### 3. Change Column Data Type
To modify the data type of an existing column:
```sql
ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;
```
### 4. Add a NOT NULL Constraint
To enforce that a column cannot accept `NULL` values:
```sql
ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL;
```
### 5. Add a UNIQUE Constraint
To ensure all values in a column or a group of columns are unique:
```sql
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE(column1, column2, ...);
```
### 6. Add a CHECK Constraint
To enforce a specific condition on the data values in a column:
```sql
ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (condition);
```
### 7. Add a PRIMARY KEY
To define a primary key constraint on one or more columns:
```sql
ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ...);
```
### 8. Drop a Constraint
To remove an existing constraint (such as a unique, check, or foreign key constraint):
```sql
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
```
> **Note for MySQL Users:** In MySQL, dropping a unique index or constraint uses a different syntax:
> ```sql
> ALTER TABLE table_name DROP INDEX constraint_name;
> ```
### 9. Drop a Primary Key
To remove the primary key constraint from a table:
```sql
ALTER TABLE table_name
DROP CONSTRAINT primary_key_constraint_name;
```
> **Note for MySQL Users:** In MySQL, dropping a primary key is simplified to:
> ```sql
> ALTER TABLE table_name DROP PRIMARY KEY;
> ```
---
## Practical Examples
Let's look at how these operations work using a sample table named `COMPANY`.
### Initial Table Setup
Assume we have a `COMPANY` table with the following data:
```sql
youtipdb=# SELECT * FROM COMPANY;
id | name | age | address | salary
----+-------+-----+------------+--------
1 | Paul | 32 | California | 20000
2 | Allen | 25 | Texas | 15000
3 | Teddy | 23 | Norway | 20000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
6 | Kim | 22 | South-Hall | 45000
7 | James | 24 | Houston | 10000
(7 rows)
```
### Example 1: Adding a Column
To add a new column named `GENDER` of type `char(1)` to the `COMPANY` table, execute the following command:
```sql
youtipdb=# ALTER TABLE COMPANY ADD GENDER char(1);
```
Now, if you query the table, you will see the new `gender` column added with default `NULL` values:
```sql
youtipdb=# SELECT * FROM COMPANY;
id | name | age | address | salary | gender
----+-------+-----+------------+--------+--------
1 | Paul | 32 | California | 20000 |
2 | Allen | 25 | Texas | 15000 |
3 | Teddy | 23 | Norway | 20000 |
4 | Mark | 25 | Rich-Mond | 65000 |
5 | David | 27 | Texas | 85000 |
6 | Kim | 22 | South-Hall | 45000 |
7 | James | 24 | Houston | 10000 |
(7 rows)
```
### Example 2: Dropping a Column
To remove the `GENDER` column we just added, run:
```sql
youtipdb=# ALTER TABLE COMPANY DROP COLUMN GENDER;
```
The table structure returns to its original state:
```sql
youtipdb=# SELECT * FROM COMPANY;
id | name | age | address | salary
----+-------+-----+------------+--------
1 | Paul | 32 | California | 20000
2 | Allen | 25 | Texas | 15000
3 | Teddy | 23 | Norway | 20000
4 | Mark | 25 | Rich-Mond | 65000
5 | David | 27 | Texas | 85000
6 | Kim | 22 | South-Hall | 45000
7 | James | 24 | Houston | 10000
(7 rows)
```
---
## Important Considerations
1. **Locking Behavior**: Running `ALTER TABLE` operations can acquire an exclusive lock on the table. For large production databases, adding columns with default values or changing column types can cause temporary downtime or block other queries.
2. **Data Type Compatibility**: When changing a column's data type using `ALTER COLUMN ... TYPE`, PostgreSQL must be able to implicitly convert the existing data to the new type. If implicit conversion is not possible, you must use the `USING` clause to specify the conversion logic (e.g., `ALTER COLUMN col TYPE integer USING col::integer`).
3. **Dropping Columns**: Dropping a column does not physically remove the data immediately; it simply makes the column invisible to SQL queries. The disk space is reclaimed during subsequent `VACUUM` operations.