1

By now, I understood there is no BOOLEAN type in mysql and internally it converts to tinyint(1) when the datatype is boolean. But I also came across that tinyint is not being handled by CAST or CONVERT function.

So basically my question is is there any direct way to typecast the datatype to TINYINT(1) the way it can be handled for any other data like DATE.

Examples :

select cast(column_name as DATE) -- Works 

select cast(column_name as tinyint) -- Doesn't work

So how can I simply convert the type to tinyint(1) without altering the table ..

Raptor
  • 53,206
  • 45
  • 230
  • 366
suraj das
  • 117
  • 1
  • 3
  • 11
  • It's weird to do so. How about this: `SELECT CONVERT(column_name, UNSIGNED INTEGER)`? This is not exactly `TINYINT`, though. – Raptor Jan 07 '20 at 06:41
  • Type values permitted in case and convert can be found here https://stackoverflow.com/questions/59622975/converting-text-to-tinyint-in-mysql tinyint is not permitted and I am wondering why you need to do this. – P.Salmon Jan 07 '20 at 06:45
  • Actually my data is getting fetched from mysql and sending to postgres. so the requirement in psql is boolean and we have an internal mechanism to convert tinyint(1) to boolean in psql.. right now the column value is text just wanted to convert it to tinyint(1) if that is possible to do so.. – suraj das Jan 07 '20 at 07:47

1 Answers1

0

Did you read the documentation for CAST() and CONVERT()?

Those functions don't support all data types. They will not cast or convert to TINYINT. The closest data type you can cast or convert to is an INTEGER (either SIGNED or UNSIGNED).

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Yes Bill, Read about CAST & CONVERT, so how can I convert text to TINYINT without altering the table.. In PSQL the field value has to be boolean, so I can't send text.. Planning to hardcode "true" & "false" instead of 1 & 0 as text.. and send the data, not sure how good it is .. !! – suraj das Jan 07 '20 at 08:51
  • In MySQL, 1 is true and 0 is false. It doesn't matter whether it's TINYINT, SMALLINT, INT, BIGINT. I don't know what you mean by PSQL. – Bill Karwin Jan 07 '20 at 14:30