Use of using when changing data types in PostgreSQL ALTER TABLE < go >

The type of transformation used to display when modifying table field types.

Original Description:

SET DATA TYPE
This form changes the type of a column of a table. Indexes and simple table constraints involving the column Willbe automatically converted for use of the new column type by rep Arsing the originally supplied expression. The optional COLLATE clause specifies a collation for the new column; If omitted, the collation is the default for the new column type. The optional USING clause specifies how to compute the new column value from the old; If omitted, the default conversion is the same as a assignment cast from old data type to new. A USING clause must be provided if there are no implicit or assignment cast from the old to new type.

The general meaning is: the conversion type when there is an implicit type conversion, will be automatically converted, if not, then you must use the using to specify the conversion rules.

1. Build a table

create table 101(id integer);

2. Inserting data

insert into tb10 select generate_series(1,5);

3. Change the ID int to varchar

alter table tb101 alter id type varchar;ALTER TABLE

Because the int-to-varchar has an implicit conversion, it can automatically convert the past.

postgres=# \d tb101
          Table "public.tb101"
 Column |       Type        | Modifiers 
--------+-------------------+-----------
 id     | character varying |

4. Change the varchar of the ID to int

 
postgres=# alter table tb101 alter id type int;
ERROR:  column "id" cannot be cast automatically to type integer
HINT:  Specify a USING expression to perform the conversion.

In the absence of an implicit conversion, you need to specify a using to display the transformation.

5. Use using for type conversion

postgres=# alter table tb101 alter id type int using id::int;
ALTER TABLE
postgres=# \d tb101
     Table "public.tb101"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | 

Id::int can also use CAST (id as int)

Use of using when changing data types in PostgreSQL ALTER TABLE < go >