PostgreSQL – Change Column Type

In this article, we will discuss the step by step process of changing the data type of a column using the ALTER TABLE statement in PostgreSQL.

Syntax:
ALTER TABLE table_name
ALTER COLUMN column_name [SET DATA] TYPE new_data_type;

Let’s analyze the above syntax:

• First, specify the name of the table to which the column you want to change belongs in the ALTER TABLE clause.
• Second, give the name of column whose data type will be changed in the ALTER COLUMN clause.
• Third, provide the new data type for the column after the TYPE keyword. It is possible to use either SET DATA TYPE or TYPE.

Example:
Let’s create a table (say, assets) and insert a few rows into it for the demonstration using the below statement:

CREATE TABLE assets (
    id serial PRIMARY KEY,
    name TEXT NOT NULL,
    asset_no VARCHAR NOT NULL,
    description TEXT,
    LOCATION TEXT,
    acquired_date DATE NOT NULL
);

INSERT INTO assets (
    NAME,
    asset_no,
    location,
    acquired_date
)
VALUES
    (
        'Server',
        '10001',
        'Server room',
        '2020-01-01'
    ),
    (
        'UPS',
        '10002',
        'Server room',
        '2020-05-16'
);

Now we will change the data type of the name column to VARCHAR, using the below statement:

ALTER TABLE assets ALTER COLUMN name TYPE VARCHAR;

Now we change the data type of description and location columns from TEXT to VARCHAR using the below statement:

ALTER TABLE assets 
    ALTER COLUMN location TYPE VARCHAR,
    ALTER COLUMN description TYPE VARCHAR;

Now we check the table for the changes made using the below statement:

SELECT * FROM assets;

Output:

My Personal Notes arrow_drop_up

Save