Options

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

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.

0

## Comments

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:

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