What is the method of converting bool types to smallint in PostgreSQL

This article mainly introduces "what is the method of converting bool type into smallint in PostgreSQL". In daily operation, I believe that many people have doubts about the method of converting bool type to smallint in PostgreSQL. Xiaobian consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the question of "what is the method of converting bool type into smallint in PostgreSQL". Next, please follow the editor to study!

Conversion process

1. Use the\ help alter table command in psql to find the syntax to modify the column type:

[local:/data/run/pg12]: 5120 pg12@testdb=#\ help alter tableCommand: ALTER TABLEDescription: change the definition of a tableSyntax:ALTER TABLE [IF EXISTS] [ONLY] name [*] action [ ... where action is one of: ADD [COLUMN] [IF NOT EXISTS] column_name data_type [COLLATE collation] [column_constraint [...]] DROP [COLUMN] [IF EXISTS] column_name [RESTRICT | CASCADE] ALTER [COLUMN] column_name [SET DATA] TYPE data_type [COLLATE collation] [USING expression] ALTER [COLUMN] column_name SET DEFAULT expression ALTER [COLUMN] column_name DROP DEFAULT ALTER [COLUMN] column_name {SET | DROP} NOT NULL ALTER [COLUMN] column_name ADD GENERATED {ALWAYS | BY DEFAULT} AS IDENTITY [(sequence_options)] ALTER [COLUMN] column_name {SET GENERATED {ALWAYS | BY DEFAULT} | SET sequence_option | RESTART [[WITH] restart]} [.] ALTER [COLUMN] column_name DROP IDENTITY [IF EXISTS] ALTER [COLUMN] column_name SET STATISTICS integer ALTER [COLUMN] column_name SET (attribute_option = value [,...]) ALTER [COLUMN] column_name RESET (attribute_option [,...]) ALTER [COLUMN] column_name SET STORAGE {PLAIN | EXTERNAL | EXTENDED | MAIN}

two。 Perform conversion

[local:/data/run/pg12]: 5120 pg12@testdb=# drop table tweets Boolter error: table "t_bool" does not exist [local:/data/run/pg12]: 5120 pg12@testdb=# create table t_bool (col bool); CREATE TABLE [local:/data/run/pg12]: 5120 pg12@testdb=# alter table t_bool alter column col type smallint ERROR: column "col" cannot be cast automatically to type smallintHINT: You might need to specify "USING col::smallint". [local: / data/run/pg12]: 5120 pg12@testdb=#

Prompt to specify "USING col::smallint" and look at the syntax of PG:

ALTER [COLUMN] column_name [SET DATA] TYPE data_type [COLLATE collation] [USING expression]

USING is followed by an expression expression, which can be understood as how to convert an expression, so execute the following statement:

[local:/data/run/pg12]: 5120 pg12@testdb=# alter table t_bool alter column col type smallint using col::int::smallint;ALTER TABLE

DONE! Successful conversion.

In fact, it can be simplified:

[local:/data/run/pg12]: 5120 pg12@testdb=# alter table t_bool alter column col type smallint using col::int;ALTER TABLE

Because there is a conversion from int to smallint, there is no need for an explicit int::smallint, but to illustrate the conversion process, the transformation will be much clearer.

Appendix

The test script is as follows:

Drop table tasking Boolash create table t_bool (col bool); at this point, the study on "what is the method of converting bool types in PostgreSQL to smallint" is over, hoping to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!