Possible Duplicate:
how to change column datatype from character to numeric in postgresql 8.4
If I have a field of type varchar (and all the values are null or string representations of numbers) how do I use alter table to convert this column type to bigint?
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.
The syntax to modify a column in a table in PostgreSQL (using the ALTER TABLE statement) is: ALTER TABLE table_name ALTER COLUMN column_name TYPE column_definition; table_name. The name of the table to modify.
How to increase the length of a character varying datatype in Postgres without data loss. Run the following command: alter table TABLE_NAME alter column COLUMN_NAME type character varying(120); This will extend the character varying column field size to 120.
The syntax for creating a unique constraint using an ALTER TABLE statement in PostgreSQL is: ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n); table_name.
To convert simply by parsing the string (casting):
alter table the_table alter column the_column type bigint using the_column::bigint
In fact, you can use any expression in terms of the_column
instead of the_column::bigint
to customise the conversion.
Note this will rewrite the table, locking out even readers until it's done.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With