WikiGalaxy
PostgreSQL Change Column Type
Understanding Column Type Changes:
Changing the column type in PostgreSQL involves modifying the data type of a column in an existing table. This is often necessary when the data requirements evolve over time.
ALTER TABLE table_name
ALTER COLUMN column_name SET DATA TYPE new_data_type;
Example 1: Integer to BigInt
Scenario:
You have a column storing integers, but now you need to store larger values that exceed the integer limit.
ALTER TABLE orders
ALTER COLUMN order_id SET DATA TYPE BIGINT;
Example 2: Text to Varchar
Scenario:
You want to change a text column to varchar for better performance with a defined limit.
ALTER TABLE users
ALTER COLUMN username SET DATA TYPE VARCHAR(50);
Example 3: Varchar to Text
Scenario:
A varchar column needs to be changed to text to accommodate longer strings without a specific limit.
ALTER TABLE products
ALTER COLUMN description SET DATA TYPE TEXT;
Example 4: Date to Timestamp
Scenario:
Convert a date column to timestamp to include time data as well.
ALTER TABLE events
ALTER COLUMN event_date SET DATA TYPE TIMESTAMP;
Example 5: Numeric to Decimal
Scenario:
Change a numeric column to decimal for precision control over financial data.
ALTER TABLE transactions
ALTER COLUMN amount SET DATA TYPE DECIMAL(10, 2);
Example 6: SmallInt to Integer
Scenario:
Upgrade a smallint column to integer to handle larger values.
ALTER TABLE inventory
ALTER COLUMN quantity SET DATA TYPE INTEGER;
Example 7: Decimal to Double Precision
Scenario:
Switch from decimal to double precision for scientific calculations requiring floating-point accuracy.
ALTER TABLE measurements
ALTER COLUMN value SET DATA TYPE DOUBLE PRECISION;
Example 8: Char to Varchar
Scenario:
Change a fixed-length char column to variable-length varchar for flexibility.
ALTER TABLE employees
ALTER COLUMN code SET DATA TYPE VARCHAR(10);
Example 9: Boolean to Integer
Scenario:
Convert a boolean column to integer for compatibility with systems expecting numeric values.
ALTER TABLE settings
ALTER COLUMN is_active SET DATA TYPE INTEGER USING is_active::integer;
Example 10: Integer to Text
Scenario:
Change an integer column to text for storing numeric values as strings.
ALTER TABLE logs
ALTER COLUMN log_id SET DATA TYPE TEXT USING log_id::text;
