JIYIK CN >

This article shows how to change a column type to another data type in Postgres.

ALTER TABLETo change the column type in Postgres, use the command

ALTER TABLE <table_name>
ALTER COLUMN <column_name> [SET DATA] TYPE <new_type>;

Use 表名, , 列名and 新类型. For example:

CREATE TABLE student(
    id SERIAL PRIMARY KEY,
    name VARCHAR NOT NULL,
    admission_date DATE NOT NULL,
    contact_no INT NOT NULL,
    description TEXT
);

Output:

postgres=# \d student
                                     Table "public.student"
     Column     |       Type        | Collation | Nullable |               Default
----------------+-------------------+-----------+----------+-------------------------------------
 id             | integer           |           | not null | nextval('student_id_seq'::regclass)
 name           | character varying |           | not null |
 admission_date | date              |           | not null |
 contact_no     | integer           |           | not null |
 description    | text              |           |          |
Indexes:
    "student_pkey" PRIMARY KEY, btree (id)
postgres=#

To change the data type of student contact number and change it to , there can be or VARCHARbetween the contact numbers .+-

So the command to change the data type of the above column is:

ALTER TABLE student
ALTER COLUMN contact_no TYPE VARCHAR;

Table Description:

postgres=# ALTER TABLE student
postgres-# ALTER COLUMN contact_no TYPE VARCHAR;
ALTER TABLE
postgres=# \d student;
                                     Table "public.student"
     Column     |       Type        | Collation | Nullable |               Default
----------------+-------------------+-----------+----------+-------------------------------------
 id             | integer           |           | not null | nextval('student_id_seq'::regclass)
 name           | character varying |           | not null |
 admission_date | date              |           | not null |
 contact_no     | character varying |           | not null |
 description    | text              |           |          |
Indexes:
    "student_pkey" PRIMARY KEY, btree (id)

postgres=#

If the table is filled with some rows and contact_noin the column you have VARCHARor non-numeric values.

If you try to contact_nochange the data type of to again int, Postgres will display an You may need to specify USING <column_name>::<data_type>error called .

Use the following SQL command to insert a row:

INSERT INTO STUDENT(name,admission_date,contact_no,description)
VALUES('John Doe','2022-01-01','1212125856 ','Lorem ipsum');

Run the statement that changes the data type:

postgres=# ALTER TABLE student
postgres-# ALTER COLUMN contact_no TYPE INT;
ERROR:  column "contact_no" cannot be cast automatically to type integer
HINT:  You might need to specify "USING contact_no::integer".
postgres=#

Therefore, you need to add this line as well.

ALTER TABLE student
ALTER COLUMN contact_no TYPE VARCHAR
USING contact_no::integer;

Now, the above SQL command will be accepted. However, VARCHARit may contain leading or trailing whitespace, so you will need to strip the whitespace.

The updated command will look like this:

ALTER TABLE student
ALTER COLUMN contact_no TYPE VARCHAR
USING (trim(contact_no)::integer);