PostgreSQL ADD COLUMN Statement

PostgreSQL ADD COLUMN Statement

The PostgreSQL ADD COLUMN statement is used to add a new column to an existing table. This statement is essential for modifying the table structure to accommodate additional data.

Syntax

ALTER TABLE table_name
ADD COLUMN column_name datatype [constraints];

The ADD COLUMN statement has the following components:

  • table_name: The name of the table to which the column will be added.
  • column_name: The name of the new column to be added.
  • datatype: The data type of the new column.
  • constraints: Optional. Any constraints for the new column, such as NOT NULL or UNIQUE.

Example PostgreSQL ADD COLUMN Statement Queries

Let's look at some examples of PostgreSQL ADD COLUMN statement queries:

1. Basic ADD COLUMN Example

ALTER TABLE employees
ADD COLUMN birth_date DATE;

This query adds a new column named birth_date of type DATE to the employees table.

2. ADD COLUMN with NOT NULL Constraint

ALTER TABLE employees
ADD COLUMN phone_number VARCHAR(15) NOT NULL;

This query adds a new column named phone_number of type VARCHAR(15) to the employees table with a NOT NULL constraint.

3. ADD COLUMN with DEFAULT Value

ALTER TABLE employees
ADD COLUMN hire_date DATE DEFAULT CURRENT_DATE;

This query adds a new column named hire_date of type DATE to the employees table with a default value of the current date.

Full Example

Let's go through a complete example that includes creating a table, adding columns, and inserting data.

Step 1: Creating a Table

This step involves creating a new table named employees to store employee data.

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

In this example, we create a table named employees with columns for id, first_name, last_name, and email.

PostgreSQL ADD COLUMN to Table

Step 2: Adding Columns

This step involves adding new columns named birth_date and phone_number to the employees table.

ALTER TABLE employees
ADD COLUMN birth_date DATE;

ALTER TABLE employees
ADD COLUMN phone_number VARCHAR(15) NOT NULL;
PostgreSQL ADD COLUMN to Table

Here, we add columns named birth_date of type DATE and phone_number of type VARCHAR(15) with a NOT NULL constraint to the employees table.

PostgreSQL ADD COLUMN to Table

Step 3: Inserting Data into the Table

This step involves inserting some sample data into the employees table.

INSERT INTO employees (first_name, last_name, email, birth_date, phone_number)
VALUES ('John', 'Doe', 'john.doe@example.com', '1990-01-01', '123-456-7890');

INSERT INTO employees (first_name, last_name, email, birth_date, phone_number)
VALUES ('Jane', 'Smith', 'jane.smith@example.com', '1985-05-15', '098-765-4321');
PostgreSQL ADD COLUMN to Table

Here, we insert data into the employees table, including values for the new columns birth_date and phone_number.

PostgreSQL ADD COLUMN to Table

Conclusion

The PostgreSQL ADD COLUMN statement is a fundamental tool for modifying the structure of an existing table to include additional data. Understanding how to use the ADD COLUMN statement and its syntax is essential for effective database schema management and modification in PostgreSQL.