Tuesday, December 07, 2010

Does Teradata Support BIT Columns? Yes and No.

In Teradata 13.0, I can create a column with a type of bit.

CREATE MULTISET TABLE test1,
    
NO FALLBACK,
    
NO BEFORE JOURNAL,
    
NO AFTER JOURNAL 

(
    
id         INT  NOT NULL,
    
bitColumn  bit

);

But did it really create a bit column? No.

Query

SELECT RequestText
FROM DBC.Tables
WHERE DatabaseName = 'rgarrison'
  
AND TableName    = 'test1';


Result

CREATE MULTISET TABLE test1,
    NO FALLBACK,
    NO BEFORE JOURNAL,
    NO AFTER JOURNAL
(
    id INT NOT NULL,
    bitColumn BYTEINT FORMAT '9'
);

Query

SELECT ColumnType, ColumnLength
FROM DBC.Columns
WHERE DatabaseName = 'rgarrison'
  
AND TableName    = 'test1'
  
AND ColumnName   = 'bitColumn';


Result

ColumnType ColumnLength
I1 1

So, Teradata will allow you to specify bit as a datatype, but it will silently translate that request to byteint.

2 comments:

  1. Another option for seeing the tables structure is this:

    SHOW TABLE test1;

    ReplyDelete
  2. The ColumnLength of 1 is deceiving. That is 1 byte of data and will accept values between -128 and 127. If you want to ensure that 1 and 0 are all that is captured you will need to include a column constraint.

    ReplyDelete