Updated April 20, 2023
Introduction to Teradata data types
When it comes to structured data Teradata is one of the famous data storage engines in the market. It is among the most popular relational database management systems around the globe. As with any other programming language or tools around us, datatypes play a major role in Teradata too. These data types provide the compiler with the knowledge of the type of data expected to be fed for processing. So as like other programming and database systems, the Teradata systems will also understand the nature of the data injected based on the data type being used upon. Here in Teradata to the datatype values are widely classified. These classifications are listed in the below coming sections. In this topic, we are going to learn about Teradata data types.
Typical Classification of Datatypes
In every database system and programming system, these datatype values are widely classified into three major sections. Even in Teradata this broader classification widely applies. These sections decide the type of data being handled upon. Almost all types of data values in and around the world can be sectioned under these three categories of data. This broad categorization is mentioned below for reference,
- Integer
- Floating point
- Character
The above-mentioned are the very broad categorizations of datatypes, these broad classifications can be subdivided further based on the exact type of data being used, When considering for Teradata systems the type of data being handled can be furtherly classified as like listed in the below-given segment,
Teradata-based datatype Classifications
1. ARRAY and VARRAY: When the data processed has more than one dimension associated with it and if all these dimensions fall under a specific data format then these come under the umbrella of array data types, In arrays, the data can be stored in both Matrix (table-like format) and even in sequential format. Here all the values in the array need to be of the same type of values.
Example: Multidimensional (n-D) VARRAY
2. Byte: In Byte the data will be stored as streams. Here no preferred translations happen to the data when stored in the Teradata database. When the data is moved from the client system it will be stored as it is it was in the client systems.
Example: Byte and BLOB objects
3. Character: All character values can be accommodated under the character datatype section. Here all the character-based values are usually stored.
Example: char and CLOB
4. Dataset: Here the data will itself have its own schema, and according to the schema mentioned the data will be stored.
Example: AVRO
5. DateTime: The DateTime category represents the various date and time-based values that can be used and stored. In Teradata, the following below-given examples below list the various date and time-based values which are allowed for storage.
Example: DATE, TIME, TIMESTAMP, TIME WITH TIME ZONE
6. Interval: The Interval datatype is concerned with values that involve a period associated with it. These data values where a period is involved can be in form of hours, minutes, seconds, days, years, months, etc.
Example: INTERVAL YEAR, INTERVAL YEAR TO MONTH, INTERVAL, INTERVAL DAY TO MINUTE, INTERVAL HOUR TO MINUTE, INTERVAL HOUR TO SECOND, INTERVAL DAY TO SECOND, INTERVAL DAY, INTERVAL MINUTE TO SECOND, INTERVAL DAY TO HOUR, INTERVAL MINUTE, INTERVAL HOUR
7. JSON: To cover all the data which falls under the JSON (JavaScript Object Notation) format in Teradata the JSON datatypes are used. So using this data type JSON based values can be represented and used.
Example: JSON
8. Numeric: For covering all the number-based data values the Numeric data types are used. So, all integer-based variants or floating precession values can be classified under the numeric category section of the
Example: Number, Smallint, Bigint, FLOAT/REAL/DOUBLE PRECISION, Decimal, Numeric, Byteint, Integer.
9. Parameter: These are data types that are passed with the method and function inputs and stored procedures or several other related items in a method.
Example: VARIANT_TYPE
10. Period: A Period information kind represents a time duration, wherein a duration is a hard and fast of contiguous time granules that extends from a starting certain as much as however now no longer such as a finishing certain.
Example: PERIOD(TIME),PERIOD(TIMESTAMP),PERIOD(TIMESTAMP WITH TIME ZONE),PERIOD(TIME WITH TIME ZONE),PERIOD(DATE)
11. UDT: UDT (User-Defined Type) statistics sorts are convention statistics sorts which you summary to version the shape and demeanor of the statistics utilized by your applications.
Example: Structured
12. XML: The XML material kind signifies XML content. The evidence is saved in a solid binary shape that jellies the records set of the XML document, which comprises the pyramid records and sort records resultant from XML validation.
Example: XML
Teradata Datatypes Example
In the Below given example, a Teradata Database is created in the Teradata systems, Here the database is created of the name EDUCBA,
Query:
CREATE DATABASE EDUCBA AS PERMANENT = 1000,SPOOL=1000;Snapshot:
A Table is created inside this database and the table is generated with classified datatypes available in Teradata, The below-given snap references the table creation process and the corresponding console output along with a response of the table notified as successfully generated,
Query:
CREATE SET TABLE EDUCBA.DATATYPES(ID INTEGER,NAME VARCHAR(100),AGE SMALLINT,DEPARTMENT VARCHAR(3),SALARY BYTE,JOINING_DATE PERIOD(DATE));Snapshot:
Query:
HELP TABLE EDUCBA.DATATYPES;Snapshot:
Insert table queries are initiated below for the table insertion process, which allows all the corresponding records to get inserted into the table. Then the snapshot below references the process of displaying the inserted records onto the console.
Query:
INSERT INTO EDUCBA.DATATYPES(ID, NAME,AGE,DEPARTMENT,SALARY,JOINING_DATE) VALUES(102,'EDWARD WASKAVAGE',47,103,'0007B23'XB,PERIOD(CURRENT_DATE))
INSERT INTO EDUCBA.DATATYPES(ID,NAME,AGE,DEPARTMENT,SALARY,JOINING_DATE) VALUES(103,'GUPTA',45,103,'0007B23'XB,PERIOD(CURRENT_DATE))
INSERT INTO EDUCBA.DATATYPES(ID,NAME,AGE,DEPARTMENT,SALARY,JOINING_DATE) VALUES(104,'HELIX',46,103,'0007B23'XB,PERIOD(CURRENT_DATE))
INSERT INTO EDUCBA.DATATYPES(ID,NAME,AGE,DEPARTMENT,SALARY,JOINING_DATE) VALUES(105,'MIKE GORDENESER',27,102,'0007B23'XB,PERIOD(CURRENT_DATE))
INSERT INTO EDUCBA.DATATYPES(ID,NAME,AGE,DEPARTMENT,SALARY,JOINING_DATE) VALUES(106,'RAMESH THILAK',56,107,'0007B23'XB,PERIOD(CURRENT_DATE))Snapshot:
Query:
SELECT * FROM EDUCBA.DATATYPES;Snapshot:
Conclusion – Teradata data types
With the rise of data and artificial intelligence the need for processing data is also getting increased significantly, to suppress this need all database-oriented enterprises are expanding their data process capabilities with new features and techniques, this impact can be widely seen in Teradata datatypes where the types and formats through which Teradata stored data can be handled are very classified and deep-rooted to data-oriented techniques.
Recommended Articles
We hope that this EDUCBA information on “Teradata data types” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
