SQL ET POSTGRE 3.11 : Change the data type in column

In this video we modify existing tables further, this time by changing the data type of a column. To do that we need an SQL query. We will change the type of the nationality column in our exemple table.

The ALTER TABLE syntax

The query starts with ALTER TABLE, followed by the table name, then on the next line ALTER COLUMN followed by the column name (nationality in our case). To change the type, we add the keyword TYPE and the new type. For a nationality, instead of storing strings like "French" or "American" in full, we can switch to a CHAR(2) to store codes such as FR. We select the query, run it and it returns successfully. We then verify with a SELECT * FROM exemple and confirm that the nationality column is now character(2). 
ALTER TABLE exemple
ALTER COLUMN nationality TYPE CHAR(2);
  • Always specify the table with ALTER TABLE.
  • Target the column with ALTER COLUMN <name> TYPE <newType>.
  • You can chain several column changes, separated by a comma.
  • Verify with SELECT * FROM <table>.
We can also change several data types in a single statement. For example, we may want to update name from VARCHAR(30) to VARCHAR(50), and email from VARCHAR(50) to VARCHAR(80). We separate both ALTER COLUMN ... TYPE ... clauses with a comma, run the query, and confirm with a final SELECT: name is now VARCHAR(50) and email VARCHAR(80). So to change a column's type, you always start by referring to the table, then to the column, then redefine its type. In the next video we will see how to delete tables from a database.

Summary

This lesson demonstrates how to modify the data type of columns in existing PostgreSQL tables using the ALTER TABLE statement. Learn to change a single column's type (such as converting nationality to CHAR(2)) and modify multiple columns in one query by separating ALTER COLUMN clauses with commas. Practical examples include changing VARCHAR sizes for name (VARCHAR(30) to VARCHAR(50)) and email columns (to VARCHAR(80)), with verification via SELECT queries.

Key points

  • Use ALTER TABLE with the table name, then ALTER COLUMN to specify the column and TYPE for the new data type
  • CHAR is ideal for fixed-length values (e.g., CHAR(2) for country codes like FR, US)
  • Multiple column types can be modified in a single query by separating each ALTER COLUMN clause with a comma
  • VARCHAR requires specifying maximum character length; examples include VARCHAR(50) for names and VARCHAR(80) for emails
  • Verify successful modifications by executing SELECT * FROM the table to inspect the updated column types

FAQ

How do you change a single column's data type in PostgreSQL?

Use ALTER TABLE table_name ALTER COLUMN column_name TYPE new_data_type. For example, ALTER TABLE example ALTER COLUMN nationality TYPE CHAR(2).

Can you modify multiple columns at once?

Yes, separate each ALTER COLUMN clause with a comma within the same ALTER TABLE statement, such as ALTER TABLE example ALTER COLUMN name TYPE VARCHAR(50), ALTER COLUMN email TYPE VARCHAR(80).

How do you confirm that column type changes were successful?

Execute SELECT * FROM table_name to view the table structure and confirm the data types have been updated correctly.