PostgreSQL Change Column Type

PostgreSQL Change Column Type

The PostgreSQL Change Column Type operation is used to modify the data type of an existing column in a table. This operation is essential for ensuring that the column data type matches the requirements of the data it stores.

Syntax

ALTER TABLE table_name
ALTER COLUMN column_name SET DATA TYPE new_data_type [USING expression];

The ALTER COLUMN SET DATA TYPE statement has the following components:

  • table_name: The name of the table containing the column to be modified.
  • column_name: The name of the column whose data type will be changed.
  • new_data_type: The new data type for the column.
  • USING expression: Optional. An expression to convert the existing column data to the new data type.

Example PostgreSQL Change Column Type Queries

Let's look at some examples of PostgreSQL ALTER COLUMN SET DATA TYPE statement queries:

1. Basic Change Column Type Example

ALTER TABLE employees
ALTER COLUMN email SET DATA TYPE TEXT;

This query changes the data type of the email column in the employees table to TEXT.

2. Change Column Type with Conversion

ALTER TABLE employees
ALTER COLUMN salary SET DATA TYPE NUMERIC(10, 2) USING salary::numeric;

This query changes the data type of the salary column in the employees table to NUMERIC with a precision of 10 and a scale of 2, using an expression to convert the existing data.

Full Example

Let's go through a complete example that includes creating a table, adding data, and then changing the data type of a column.

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),
    salary INTEGER
);

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

PostgreSQL Change Column Type

Step 2: Inserting Data into the Table

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

INSERT INTO employees (first_name, last_name, email, salary)
VALUES ('John', 'Doe', 'john.doe@example.com', 50000);

INSERT INTO employees (first_name, last_name, email, salary)
VALUES ('Jane', 'Smith', 'jane.smith@example.com', 60000);

Here, we insert data into the employees table, including values for the salary column.

PostgreSQL Change Column Type

Step 3: Changing the Column Type

This step involves changing the data type of the salary column to NUMERIC.

ALTER TABLE employees
ALTER COLUMN salary SET DATA TYPE NUMERIC(10, 2) USING salary::numeric;

Here, we change the data type of the salary column in the employees table to NUMERIC with a precision of 10 and a scale of 2, using an expression to convert the existing data.

PostgreSQL Change Column Type

Conclusion

The PostgreSQL Change Column Type operation is a fundamental tool for modifying the structure of an existing table to ensure data integrity and compatibility. Understanding how to use the ALTER COLUMN SET DATA TYPE statement and its syntax is essential for effective database schema management and modification in PostgreSQL.