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.
Which data type is appropriate for just holding 1 or 0?
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...
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...
+1Hi 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.
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! :-)
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
