Hello,
As a test I am trying to move a small MySql database to Vertica. I am having a lot of issues with data types. Can someone please help with these questions? What is the Vertica equivalent to the data type 'tinyint'? And what is the equivalent to 'unsigned'?
Thanks again for your help.
Converting from MySQL datatype question
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Converting from MySQL datatype question
In the Vertica database INT, INTEGER, INT8, BIGINT, SMALLINT and TINYINT are all synonyms for the same signed 64-bit integer data type. You don't have to worry about specifying a size because Vertica will automatically compress the data to conserve disk space when you don't need the full 64 bits.
If I create a table having 6 columns that use the INT, INTEGER, INT8, BIGINT, SMALLINT and TINYINT data types and then do a describe on the table, you can see that they are all actually using the INT data type with a size of 8. Note that this is 8 bytes and not 8 digits! So the range of values for an INT in Vertica is -2^63+1 to 2^63-1.
Example:
As far as the UNSIGNED column attribute, there is no Vertica equivalent that I am aware of... But there is no need to worry. The unsigned INT range in MySQL is only 0 to 4,294,967,295. In Vertica the range is up to 2^63 where 2^63 is a whopping 9,223,372,036,854,775,808 (19 digits)!
If I create a table having 6 columns that use the INT, INTEGER, INT8, BIGINT, SMALLINT and TINYINT data types and then do a describe on the table, you can see that they are all actually using the INT data type with a size of 8. Note that this is 8 bytes and not 8 digits! So the range of values for an INT in Vertica is -2^63+1 to 2^63-1.
Example:
Code: Select all
dbadmin=> create table test (test1 INT, test2 INTEGER, test3 INT8, test4 BIGINT, test5 SMALLINT, test6 TINYINT);
CREATE TABLE
dbadmin=> \d test;
List of Fields by Tables
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+------+------+---------+----------+-------------+-------------
public | test | test1 | int | 8 | | f | f |
public | test | test2 | int | 8 | | f | f |
public | test | test3 | int | 8 | | f | f |
public | test | test4 | int | 8 | | f | f |
public | test | test5 | int | 8 | | f | f |
public | test | test6 | int | 8 | | f | f |
(6 rows)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.