Converting from MySQL datatype question

Moderator: NorbertKrupa

Post Reply
User avatar
usli06
Intermediate
Intermediate
Posts: 93
Joined: Wed Jan 25, 2012 4:53 am

Converting from MySQL datatype question

Post by usli06 » Fri Feb 10, 2012 11:42 pm

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.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1778
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Converting from MySQL datatype question

Post by JimKnicely » Mon Feb 13, 2012 3:22 pm

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:

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)
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)!
Jim Knicely

Image

Note: I work for HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Migration”