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?

Thanks.

Comments

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


  • Hi Ozi -

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

    Happy Playing ...

    marco

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

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

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

  • Int is the best type. 

    Vertica perfroms better with numric types. 

     

    Eugenia

  • 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
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.