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

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?



  • Options
    Abhishek_RanaAbhishek_Rana Vertica Employee Employee
    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'.


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

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

    dbadmin=> commit;
    dbadmin=> select * from bool;
    (2 rows)

    For more information on boolean type refer to below link:


  • Options
    marcothesanemarcothesane - Select Field - Administrator

    Hi Ozi -

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

    Happy Playing ...


  • Options

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

  • Options

    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..

  • Options

    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.

  • Options

    Int is the best type. 

    Vertica perfroms better with numric types. 



  • Options
    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! :-)

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file