Which data type is appropriate for just holding 1 or 0?

ozi_samur

Hi guys,

I have a table which has columns like IS_ENABLED etc. which holds just 1 or 0. In SQLServer there is BIT data type it holds 1 or 0 as BIT. In vertica I could not see that data type and I used the INTEGER data type which is 8 byte. What data type should I use for this purpose?

Thanks.

Abhishek_Rana

In vertica we do have Boolean data type, which can be appropriate for usage in this case.

SQL type BOOLEAN, which has two states: true and false. The third state in SQL boolean logic is unknown, which is represented by the NULL value.

You can insert the values in it as o & 1 ,wheras the results will show you values as 'f' & 't'.

Example:


dbadmin=> create table bool(bo boolean);CREATE TABLE
dbadmin=>
dbadmin=> insert into bool values(1);
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into bool values(0);
 OUTPUT
--------
      1
(1 row)

dbadmin=> commit;
COMMIT
dbadmin=> select * from bool;
 bo
----
 t
 f
(2 rows)


For more information on boolean type refer to below link:

http://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/SQLReferenceManual/DataTypes/BooleanDataTy...

marcothesane

Hi Ozi -

The type you're looking for is Boolean - with the values {'true', 'false'}, '{'t','f'} or {0,1}.

Happy Playing ...

marco

rsaveetha

But i still want to display it as 1 and 0 instead of t and f. How can i achieve it?

FiliN

The following type conversions are not allowed:

• Boolean to other types
• DATE/TIME type conversion
• Approximate numeric type conversions
• Conversions between BINARY and VARBINARY

I think the only option left is case when 't' then 1 and so on..

HerbC

I see that in the configuration_parameters system table, the CURRENT_VALUE, DATABASE_VALUE and DEFAULT_VALUE columns are all VARCHAR.  These values often display as '1' or '0'.  So, following Vertica's example, VARCHAR might be the way to go.

[Deleted User]

Int is the best type. 

Vertica perfroms better with numric types. 

 

Eugenia

HerbC

Looks like we have a number of opinions here. But, I would go with Eugenia's advice. She has major field experience. I bow down before her knowledge! :-)