WikiGalaxy
PostgreSQL ADD Column
Introduction:
In PostgreSQL, the ADD COLUMN operation allows you to add a new column to an existing table. This operation is part of the ALTER TABLE command and is essential for modifying table structures without losing data.
Syntax:
The basic syntax for adding a column is:
ALTER TABLE table_name
ADD COLUMN column_name data_type;
Example 1: Adding a Simple Column
Scenario:
You have a table named employees and want to add a column for storing the department name.
Solution:
ALTER TABLE employees
ADD COLUMN department VARCHAR(50);
This command adds a new column named department with a data type of VARCHAR and a maximum length of 50 characters.
Example 2: Adding a Column with Default Value
Scenario:
You want to add a column to the orders table to track order status, defaulting to 'Pending'.
Solution:
ALTER TABLE orders
ADD COLUMN status VARCHAR(20) DEFAULT 'Pending';
Here, the status column is added with a default value of 'Pending', ensuring that all new records will have this value unless specified otherwise.
Example 3: Adding a NOT NULL Column
Scenario:
Add a column to the products table to store SKU numbers, ensuring each entry is not null.
Solution:
ALTER TABLE products
ADD COLUMN sku VARCHAR(30) NOT NULL;
The sku column is added with a NOT NULL constraint, which means every record must have a SKU value.
Example 4: Adding a Column with Unique Constraint
Scenario:
You need to add an email column to the users table, ensuring all emails are unique.
Solution:
ALTER TABLE users
ADD COLUMN email VARCHAR(100) UNIQUE;
The email column is added with a UNIQUE constraint, ensuring no duplicate email addresses in the table.
Example 5: Adding a Column with Check Constraint
Scenario:
Add an age column to the customers table, ensuring age is always positive.
Solution:
ALTER TABLE customers
ADD COLUMN age INT CHECK (age > 0);
The age column is added with a CHECK constraint that enforces the age to be greater than zero.
Example 6: Adding a Column with Foreign Key
Scenario:
You want to add a column to the orders table to reference the customer ID from the customers table.
Solution:
ALTER TABLE orders
ADD COLUMN customer_id INT REFERENCES customers(id);
The customer_id column is added with a FOREIGN KEY constraint, linking it to the id column in the customers table.
Example 7: Adding Multiple Columns
Scenario:
You need to add multiple columns to the projects table for tracking start and end dates.
Solution:
ALTER TABLE projects
ADD COLUMN start_date DATE,
ADD COLUMN end_date DATE;
This command adds two new columns, start_date and end_date, both with a DATE data type.
Example 8: Adding a Column with Serial Data Type
Scenario:
To automatically generate unique values, you want to add a column to the invoices table.
Solution:
ALTER TABLE invoices
ADD COLUMN invoice_number SERIAL;
The invoice_number column is added with a SERIAL data type, which automatically generates a unique identifier for each row.
Example 9: Adding a Column with JSONB Data Type
Scenario:
You want to add a column to the settings table to store configuration data in JSON format.
Solution:
ALTER TABLE settings
ADD COLUMN config JSONB;
The config column is added with a JSONB data type, allowing you to store JSON formatted data efficiently.
Example 10: Adding a Column with Array Data Type
Scenario:
You want to add a column to the students table to store multiple phone numbers for each student.
Solution:
ALTER TABLE students
ADD COLUMN phone_numbers TEXT[];
The phone_numbers column is added with an array data type, allowing storage of multiple phone numbers as an array of text.
