Data Types in SQL - Javagyansite

In this article, we will explore different Data Types in SQL. Let’s get started.

Various Data Types in SQL

SQL uses data types to specify the type of data that can be stored in a column of a table or a variable. Data types are crucial for ensuring data integrity, optimizing storage, and defining the operations that can be performed on the data. SQL supports a wide range of data types, and here are some common ones:

INTEGER (INT)

Represents whole numbers, both positive and negative. Examples include 1, -5, 1000.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT,
    salary INT
);

INSERT INTO employees (employee_id, first_name, last_name, age, salary)
VALUES
    (1, 'Alice', 'Johnson', 28, 55000),
    (2, 'Bob', 'Smith', 35, 60000),
    (3, 'Charlie', 'Brown', 30, 58000);

In this example, we have created a table named employees to store information about employees. The columns employee_id, age, and salary are defined with the INT data type, which is used to store integer values, including whole numbers like employee IDs, ages, and salaries.

We then insert sample data into the table for three employees, including their employee IDs, ages, and salaries. The INT data type is commonly used for storing integers in SQL and is suitable for a wide range of whole number values.

SMALLINT

Similar to INTEGER but uses less storage space. It is suitable for smaller numbers.

CREATE TABLE student_scores (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(255),
    math_score SMALLINT,
    english_score SMALLINT,
    history_score SMALLINT
);

INSERT INTO student_scores (student_id, student_name, math_score, english_score, history_score)
VALUES
    (1, 'Alice Johnson', 90, 85, 92),
    (2, 'Bob Smith', 88, 78, 85),
    (3, 'Charlie Brown', 95, 89, 91);

In this example, we have created a table named student_scores to store scores for different subjects for students. The columns math_score, english_score, and history_score are defined with the SMALLINT data type, which is used to store small whole numbers. In this case, it is used to store student scores for these subjects. The data type SMALLINT is appropriate for storing scores because it typically covers a small range of values, and it is more memory-efficient than larger integer types like INT or BIGINT.

We then insert sample data into the table for three students, including their scores in math, English, and history.

SMALLINT is supported in various relational database management systems (RDBMS):

PostgreSQL: Supports SMALLINT for storing 2-byte signed integers in the range of -32,768 to 32,767.
MySQL: Supports SMALLINT for 2-byte signed integers with the same range as PostgreSQL.
SQL Server: Supports SMALLINT for 2-byte signed integers with the same range as PostgreSQL.
Oracle Database: Typically uses the NUMBER data type with specified precision and scale to store small integers, as it doesn’t have a dedicated SMALLINT type.
SQLite: Supports SMALLINT for 2-byte signed integers with the same range as PostgreSQL.
IBM Db2: Supports SMALLINT for 2-byte signed integers with the same range as PostgreSQL.

BIGINT

Stores large integer values that might not fit in an INTEGER or SMALLINT data type.

CREATE TABLE customers (
    customer_id BIGINT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    total_purchase_amount BIGINT
);

INSERT INTO customers (customer_id, first_name, last_name, total_purchase_amount)
VALUES
    (1, 'Alice', 'Johnson', 1000000000), -- 1 billion
    (2, 'Bob', 'Smith', 750000000),      -- 750 million
    (3, 'Charlie', 'Brown', 850000000);  -- 850 million

In this example, we have created a table named customers to store information about customers. The customer_id and total_purchase_amount columns are defined with the BIGINT data type.

The BIGINT data type is used to store large integer values, typically larger than what can be accommodated by the INT data type. It’s suitable for situations where you need to store very large whole number values, such as financial amounts, counters, or IDs that could grow to very high values.

In the INSERT INTO statement, we insert sample data for three customers, including their customer IDs and total purchase amounts, which are represented as BIGINT values.

BIGINT is supported in various relational database management systems (RDBMS):

PostgreSQL: Supports BIGINT for storing 8-byte signed integers in the range of approximately -9.2 quadrillion to 9.2 quadrillion.
MySQL: Supports BIGINT for 8-byte signed integers with the same range as PostgreSQL.
SQL Server: Supports BIGINT for 8-byte signed integers with the same range as PostgreSQL.
Oracle Database: Typically uses the NUMBER data type with specified precision and scale to store large integers, as it doesn’t have a dedicated BIGINT type.
SQLite: Supports BIGINT for 8-byte signed integers with the same range as PostgreSQL.
IBM Db2: Supports BIGINT for 8-byte signed integers with the same range as PostgreSQL.

FLOAT

Represents floating-point numbers with decimal places. It is used for approximate values. Example: 3.14159.

CREATE TABLE product_prices (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    price_per_unit FLOAT
);

INSERT INTO product_prices (product_id, product_name, price_per_unit)
VALUES
    (1, 'Widget A', 19.99),
    (2, 'Widget B', 12.49),
    (3, 'Widget C', 8.99);

In this example, we have created a table named product_prices to store information about products and their prices. The price_per_unit column is defined with the FLOAT data type.

The FLOAT data type is used to store approximate numeric values, including numbers with fractional parts (decimal numbers). It’s suitable for storing values like product prices, measurements, or any other data where precision beyond the integer level is required.

In the INSERT INTO statement, we insert sample data for three products, including their product IDs and prices per unit, which are represented as FLOAT values. These prices can have decimal parts, as seen in the example.

FLOAT is supported in various relational database management systems (RDBMS):

PostgreSQL: Supports the FLOAT data type for representing approximate numeric values with decimal places.
MySQL: Provides the FLOAT data type for approximate numeric values, and it also has a DOUBLE data type for double-precision floating-point numbers.
SQL Server: Supports the FLOAT data type for storing approximate numeric values, including decimal places.
Oracle Database: Offers the BINARY_FLOAT and BINARY_DOUBLE data types for single-precision and double-precision floating-point numbers, respectively.
SQLite: Supports the REAL data type, which is used for single-precision floating-point numbers, similar to FLOAT.
IBM Db2: Provides the REAL and DOUBLE data types for single-precision and double-precision floating-point numbers, respectively.

REAL

The REAL data type is used to store approximate numeric values with single-precision floating-point representation in SQL. It is typically used to store values with fractional parts, similar to the FLOAT data type, but with less precision. REAL occupies 4 bytes of storage and has a range and precision suitable for many applications where exact numeric precision is not required.

Here’s an example of using the REAL data type in SQL:

CREATE TABLE temperature_readings (
    reading_id INT PRIMARY KEY,
    location VARCHAR(100),
    temperature REAL
);

INSERT INTO temperature_readings (reading_id, location, temperature)
VALUES
    (1, 'City A', 28.5),
    (2, 'City B', 32.1),
    (3, 'City C', 26.8);

In this example, we have created a table named temperature_readings to store temperature data for different locations. The temperature column is defined with the REAL data type.

The following relational database management systems (RDBMS) commonly support the REAL data type:

Microsoft SQL Server: In SQL Server, you can use the REAL data type to store single-precision floating-point numbers.
PostgreSQL: PostgreSQL also supports the REAL data type for single-precision floating-point numbers.
Oracle Database: Oracle Database provides the FLOAT data type, which is similar to REAL and is used for approximate numeric values with single-precision floating-point representation.
MySQL: MySQL offers the FLOAT data type for single-precision floating-point numbers, which is functionally similar to REAL.

DOUBLE PRECISION

The DOUBLE PRECISION data type is used to store double-precision floating-point numbers in SQL. It provides a higher level of precision compared to the REAL data type, making it suitable for applications that require more significant numeric precision. DOUBLE PRECISION typically occupies 8 bytes of storage.

Here’s an example of using the DOUBLE PRECISION data type in SQL:

CREATE TABLE scientific_data (
    data_id INT PRIMARY KEY,
    experiment_name VARCHAR(100),
    measurement DOUBLE PRECISION
);

INSERT INTO scientific_data (data_id, experiment_name, measurement)
VALUES
    (1, 'Experiment A', 0.000123456789),
    (2, 'Experiment B', 12345.6789),
    (3, 'Experiment C', -9876.54321);

In this example, we have created a table named scientific_data to store scientific measurement data. The measurement column is defined with the DOUBLE PRECISION data type.

The following relational database management systems (RDBMS) commonly support the DOUBLE PRECISION data type:

PostgreSQL: PostgreSQL supports the DOUBLE PRECISION data type for double-precision floating-point numbers.
Microsoft SQL Server: SQL Server provides the FLOAT data type, which is used for approximate numeric values with double-precision floating-point representation. While it’s not named DOUBLE PRECISION, it serves a similar purpose.
Oracle Database: Oracle Database offers the DOUBLE PRECISION data type, which is synonymous with the NUMBER data type with a specified precision and scale. Oracle also supports the BINARY_DOUBLE data type for double-precision floating-point numbers.
MySQL: MySQL provides the DOUBLE data type for double-precision floating-point numbers, which is equivalent to DOUBLE PRECISION.

DECIMAL (DEC) or NUMERIC

The DECIMAL (often referred to as DEC) and NUMERIC data types in SQL are used for storing fixed-point numbers with exact precision. Unlike FLOAT or DOUBLE PRECISION, which are approximate numeric types, DECIMAL and NUMERIC provide precise storage for numbers with a specific number of decimal places.

Here’s an example of using the DECIMAL data type in SQL:

CREATE TABLE financial_data (
    transaction_id INT PRIMARY KEY,
    transaction_amount DECIMAL(10, 2)
);

INSERT INTO financial_data (transaction_id, transaction_amount)
VALUES
    (1, 100.25),
    (2, 75.50),
    (3, 1234.67);

In this example, we have created a table named financial_data to store financial transaction data. The transaction_amount column is defined with the DECIMAL(10, 2) data type, which means it can store numbers with up to 10 digits in total, where 2 of those digits are reserved for the decimal portion.

The NUMERIC data type is essentially the same as DECIMAL and is often used interchangeably. Both types are used for precise decimal calculations.

Here are some relational database management systems (RDBMS) that support the DECIMAL or NUMERIC data types:

PostgreSQL: PostgreSQL supports both DECIMAL and NUMERIC data types with customizable precision and scale.
Microsoft SQL Server: SQL Server provides the DECIMAL data type, which is used for exact numeric values with a specified precision and scale.
Oracle Database: Oracle Database supports the NUMBER data type, which is similar to DECIMAL and NUMERIC and allows you to specify precision and scale.
MySQL: MySQL offers the DECIMAL and NUMERIC data types, which are used for exact numeric storage with user-defined precision and scale.
SQLite: SQLite supports the NUMERIC and DECIMAL data types, which are used for precise numeric values.

CHARACTER (CHAR)

The CHARACTER data type, often referred to as CHAR, is used in SQL to store fixed-length character strings. Unlike variable-length string types like VARCHAR, which only use as much space as needed to store the string, CHAR always reserves a fixed amount of space, padding shorter strings with spaces.

Here’s an example of using the CHAR data type in SQL:

CREATE TABLE employee (
    employee_id INT PRIMARY KEY,
    first_name CHAR(50),
    last_name CHAR(50)
);

INSERT INTO employee (employee_id, first_name, last_name)
VALUES
    (1, 'John', 'Doe'),
    (2, 'Alice', 'Smith'),
    (3, 'Bob', 'Johnson');

In this example, we have created a table named employee to store employee data. The first_name and last_name columns are defined with the CHAR(50) data type, indicating that they can store character strings with a fixed length of 50 characters. If a name is shorter than 50 characters, it will be padded with spaces to fill the fixed length.

VARCHAR

The VARCHAR data type in SQL is used to store variable-length character strings. Unlike the CHAR data type, which reserves a fixed amount of space for each value, VARCHAR only uses as much space as needed to store the actual string, making it more storage-efficient for shorter strings.

Here’s an example of using the VARCHAR data type in SQL:

CREATE TABLE product (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255),
    description VARCHAR(500)
);

INSERT INTO product (product_id, product_name, description)
VALUES
    (1, 'Laptop', 'A powerful laptop for work and gaming.'),
    (2, 'Smartphone', 'The latest smartphone with advanced features.'),
    (3, 'Headphones', 'High-quality headphones for music lovers.');

In this example, we have created a table named product to store product data. The product_name and description columns are defined with the VARCHAR data type, specifying a maximum length of 255 characters for product_name and 500 characters for description. These columns will only use as much storage space as required to store the actual text.

TEXT

The TEXT data type in SQL is used to store large amounts of variable-length text data. It is particularly suitable for storing long strings, such as paragraphs of text, articles, or even entire documents.

Here’s an example of using the TEXT data type in SQL:

CREATE TABLE blog_post (
    post_id INT PRIMARY KEY,
    title VARCHAR(255),
    content TEXT
);

INSERT INTO blog_post (post_id, title, content)
VALUES
    (1, 'Introduction to SQL', 'SQL (Structured Query Language) is a powerful language used for managing relational databases...'),
    (2, 'Advanced SQL Techniques', 'In this article, we will explore some advanced SQL techniques for optimizing queries...'),
    (3, 'Database Design Best Practices', 'Designing a well-structured database is essential for efficient data management...');

In this example, we have a table named blog_post with a content column defined as TEXT. This column is used to store the content of blog posts, which can vary significantly in length.

While CHAR and VARCHAR are appropriate for storing text data with known or maximum lengths, TEXT is used when dealing with potentially very long text values where the length is unpredictable or can vary greatly, such as in blog posts or document storage. TEXT is more storage-efficient for such purposes and doesn’t impose a maximum length limit.

DATE

The DATE data type in SQL is used to store date values, including the year, month, and day. It does not store time information, making it suitable for representing dates like birthdays, order dates, or event dates.

Here’s an example of using the DATE data type in SQL:

CREATE TABLE events (
    event_id INT PRIMARY KEY,
    event_name VARCHAR(255),
    event_date DATE
);

INSERT INTO events (event_id, event_name, event_date)
VALUES
    (1, 'Birthday Party', '2023-09-20'),
    (2, 'Company Conference', '2023-10-15'),
    (3, 'Product Launch', '2023-11-05');

In this example, we have a table named events with a column event_date of type DATE. This column is used to store the date of various events. You can insert date values in the format ‘YYYY-MM-DD,’ where ‘YYYY’ represents the year, ‘MM’ represents the month, and ‘DD’ represents the day.

You can perform various operations with DATE values in SQL, such as comparing dates, calculating date differences, and formatting dates for display. The DATE data type is supported by most relational database management systems, including PostgreSQL, MySQL, SQL Server, and Oracle.

TIME

The TIME data type in SQL is used to store time values, including hours, minutes, seconds, and fractions of seconds. It does not store date information, making it suitable for representing times of day, durations, or other time-related data.

Here’s an example of using the TIME data type in SQL:

CREATE TABLE appointments (
    appointment_id INT PRIMARY KEY,
    patient_name VARCHAR(255),
    appointment_time TIME
);

INSERT INTO appointments (appointment_id, patient_name, appointment_time)
VALUES
    (1, 'John Doe', '15:30:00'),
    (2, 'Alice Smith', '09:45:00'),
    (3, 'Bob Johnson', '13:15:00');

In this example, we have a table named appointments with a column appointment_time of type TIME. This column is used to store the time of various appointments. You can insert time values in the format ‘HH:MM:SS,’ where ‘HH’ represents hours in 24-hour format, ‘MM’ represents minutes, ‘SS’ represents seconds, and you can also include fractions of seconds.

You can perform various operations with TIME values in SQL, such as comparing times, calculating time differences, and formatting times for display. The TIME data type is supported by most relational database management systems, including PostgreSQL, MySQL, SQL Server, and Oracle.

DATETIME or TIMESTAMP

The DATETIME or TIMESTAMP data type in SQL is used to store both date and time values, including year, month, day, hour, minute, second, and fractions of seconds. This data type is suitable for representing specific moments in time, such as timestamps for events or transactions.

Here’s an example of using the TIMESTAMP data type in SQL:

CREATE TABLE transactions (
    transaction_id INT PRIMARY KEY,
    transaction_amount DECIMAL(10, 2),
    transaction_datetime TIMESTAMP
);

INSERT INTO transactions (transaction_id, transaction_amount, transaction_datetime)
VALUES
    (1, 100.50, '2023-09-20 09:45:00'),
    (2, 75.25, '2023-09-20 14:30:15'),
    (3, 50.00, '2023-09-21 08:15:30');

In this example, we have a table named transactions with a column transaction_datetime of type TIMESTAMP. This column is used to store the date and time of various financial transactions. You can insert timestamp values in the format 'YYYY-MM-DD HH:MM:SS', where 'YYYY' represents the year, 'MM' represents the month, 'DD' represents the day, 'HH' represents hours in 24-hour format, 'MM' represents minutes, 'SS' represents seconds, and you can also include fractions of seconds.

You can perform various operations with TIMESTAMP values in SQL, such as comparing timestamps, calculating time intervals, and formatting timestamps for display. The TIMESTAMP or DATETIME data type is supported by most relational database management systems, including PostgreSQL, MySQL, SQL Server, and Oracle.

The choice between TIMESTAMP and DATETIME depends on your specific requirements. If you need higher precision and better timezone support, especially in applications where data may be used in various time zones, TIMESTAMP is a better choice. However, if you need a simple and portable solution without timezone considerations, DATETIME may be more suitable.

BOOLEAN

The BOOLEAN data type is used to represent true/false or binary values in SQL. It typically has two possible values: TRUE and FALSE. Some databases, like PostgreSQL, also accept TRUE, FALSE, and NULL. Here’s an example of using the BOOLEAN data type:

-- Creating a table with a BOOLEAN column
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(255),
    is_active BOOLEAN
);

-- Inserting data with BOOLEAN values
INSERT INTO employees (employee_id, employee_name, is_active)
VALUES
    (1, 'John Doe', TRUE),
    (2, 'Alice Smith', FALSE),
    (3, 'Bob Johnson', TRUE);

In this example:

We create a table named employees with a column is_active of type BOOLEAN to indicate whether an employee is active or not.
We insert data into the table with TRUE and FALSE values representing the employee’s active status.

BINARY

The BINARY data type is used to store binary data, such as raw binary files, images, or other non-textual data. It is typically used to store large amounts of binary data in a database. Here’s an example of using the BINARY data type:

-- Creating a table with a BINARY column
CREATE TABLE binary_data (
    data_id INT PRIMARY KEY,
    binary_content BINARY
);

-- Inserting binary data into the table
INSERT INTO binary_data (data_id, binary_content)
VALUES
    (1, 0x48656C6C6F), -- Hexadecimal representation of "Hello"
    (2, 0x776F726C64); -- Hexadecimal representation of "world"

In this example:

We create a table named binary_data with a column binary_content of type BINARY.
We insert binary data into the table using hexadecimal representations. For instance, 0x48656C6C6F represents the string “Hello” in hexadecimal.

The BINARY data type or its equivalents for storing binary data is supported by various RDBMS systems:

MySQL: Supports the BINARY data type.
PostgreSQL: Offers the BYTEA data type for binary data storage.
SQL Server: Provides VARBINARY and BINARY data types.
Oracle Database: Uses the RAW data type for fixed-length binary data.
SQLite: Supports the BLOB data type for binary data.

BLOB (Binary Large Object)

The BLOB (Binary Large Object) data type is used for storing large binary data, such as images, audio, video, or other binary files, in a database. It’s typically used when you need to store and retrieve binary data in its original format.

Here’s an example of using the BLOB data type to store an image in a table:

CREATE TABLE images (
    image_id INT PRIMARY KEY,
    image_data BLOB
);

INSERT INTO images (image_id, image_data)
VALUES (1, '0x89504E470D0A...'); -- Insert binary image data

RDBMS Support:

MySQL: Supports the BLOB data type.
PostgreSQL: Uses the BYTEA data type, which is similar to BLOB.
SQL Server: Offers the VARBINARY(MAX) data type for large binary data.
Oracle Database: Provides the BLOB data type.
SQLite: Supports the BLOB data type.

Difference from BINARY:

BINARY is typically used for fixed-length binary data, while BLOB is designed for variable-length binary data like images or files.
BINARY stores binary data in its raw form, whereas BLOB can store large amounts of binary data and is often used for files or multimedia.
BLOB is more suitable for situations where the size of the binary data may vary significantly.

ENUM

The ENUM data type is used to define a list of possible values for a column, and each value must belong to that predefined list. It’s a way to restrict the possible values that can be inserted into a column. Here’s an example of using ENUM:

CREATE TABLE colors (
    color_id INT PRIMARY KEY,
    color_name ENUM('Red', 'Green', 'Blue', 'Yellow', 'Orange')
);

INSERT INTO colors (color_id, color_name)
VALUES (1, 'Red'), (2, 'Blue'), (3, 'Green');

In this example, the color_name column can only have values from the specified list: ‘Red’, ‘Green’, ‘Blue’, ‘Yellow’, or ‘Orange’. Any other value will result in an error.

RDBMS Support:

MySQL: Supports the ENUM data type.
PostgreSQL: Does not have a built-in ENUM data type, but you can achieve similar functionality using custom types or check constraints.
SQL Server: Does not have a built-in ENUM data type, but you can use a VARCHAR column with a check constraint for similar functionality.
Oracle Database: Does not have a built-in ENUM data type, but you can use a VARCHAR column with a check constraint.
SQLite: Does not have a built-in ENUM data type, but you can use a TEXT column with a check constraint.

ARRAY

The ARRAY data type is used to store an ordered collection of elements within a single column. It’s supported in various relational database management systems (RDBMS), though the syntax and capabilities may vary between systems. Here’s an example of using the ARRAY data type in PostgreSQL:

-- Create a table with an array column
CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    book_title VARCHAR(255),
    authors TEXT[]
);

-- Insert data with arrays
INSERT INTO books (book_title, authors)
VALUES
    ('Book 1', ARRAY['Author A', 'Author B']),
    ('Book 2', ARRAY['Author C']),
    ('Book 3', ARRAY['Author D', 'Author E']);

-- Querying data from the array column
SELECT book_title, authors[1] AS first_author
FROM books;

In this PostgreSQL example:

We create a table named books with an authors column of type TEXT[], which represents an array of text values.
We insert data into the authors column as arrays of author names for each book.
We query the data, retrieving the book title and the first author from each array.

RDBMS Support:

PostgreSQL: Supports the ARRAY data type.
MySQL: Supports the ARRAY data type starting from MySQL 5.7. MySQL calls it the JSON array.
SQL Server: Supports the ARRAY data type starting from SQL Server 2016 with the introduction of JSON support.
Oracle Database: Supports the ARRAY data type with varying capabilities, such as using collections or nested tables.
SQLite: Does not have a native ARRAY data type, but you can store arrays as serialized text or use a separate table to represent arrays.

JSON

The JSON data type is used to store JSON (JavaScript Object Notation) data within a column. It allows for structured and flexible storage of data. JSON data type support varies between relational database management systems (RDBMS). Here’s an example of using the JSON data type in PostgreSQL:

-- Create a table with a JSON column
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(255),
    product_details JSON
);

-- Insert data with JSON values
INSERT INTO products (product_name, product_details)
VALUES
    ('Product A', '{"color": "red", "price": 19.99, "in_stock": true}'),
    ('Product B', '{"color": "blue", "price": 24.99, "in_stock": false}');

-- Querying JSON data
SELECT product_name, product_details->>'color' AS color
FROM products;

In this PostgreSQL example:

We create a table named products with a product_details column of type JSON, which allows us to store JSON-formatted data.
We insert data into the product_details column as JSON objects representing product details.
We query the data, extracting the color property from the JSON objects.

RDBMS Support:

PostgreSQL: Supports the JSON and JSONB data types. The JSONB type is optimized for storage and querying.
MySQL: Supports the JSON data type for storing JSON data.
SQL Server: Supports the JSON data type for storing JSON data.
Oracle Database: Supports JSON data storage and querying using the BLOB and CLOB data types.
SQLite: Supports the JSON1 extension, which provides JSON-related functions and indexing but doesn’t have a native JSON data type.

UUID

The UUID (Universally Unique Identifier) data type is used to store unique identifiers in a standardized format. Support for UUID data type varies among relational database management systems (RDBMS). Here’s an example of using the UUID data type in PostgreSQL:

-- Create a table with a UUID column
CREATE TABLE employees (
    employee_id UUID PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

-- Insert data with UUID values
INSERT INTO employees (employee_id, first_name, last_name)
VALUES
    ('550e8400-e29b-41d4-a716-446655440000', 'John', 'Doe'),
    ('550e8400-e29b-41d4-a716-446655440001', 'Alice', 'Smith');

In this PostgreSQL example:

We create a table named employees with an employee_id column of type UUID, which allows us to store UUID values.
We insert data into the employee_id column as UUIDs representing unique employee identifiers.

RDBMS Support:

PostgreSQL: Provides native support for the UUID data type.
MySQL: Supports the UUID data type starting from MySQL 8.0.0.
SQL Server: Supports the uniqueidentifier data type, which is similar to UUID, using the NEWID() function to generate new values.
Oracle Database: Does not have a native UUID data type, but UUIDs can be stored as RAW or VARCHAR2.
SQLite: Does not have a native UUID data type, but UUIDs can be stored as TEXT or BLOB.

INTERVAL

The INTERVAL data type is used to represent a duration or a time span in various units, such as days, hours, minutes, and seconds. Support for the INTERVAL data type varies among relational database management systems (RDBMS). Here’s an example of using the INTERVAL data type in PostgreSQL:

-- Create a table with an INTERVAL column
CREATE TABLE tasks (
    task_id SERIAL PRIMARY KEY,
    task_name VARCHAR(255),
    duration INTERVAL
);

-- Insert data with INTERVAL values
INSERT INTO tasks (task_name, duration)
VALUES
    ('Task A', '3 days 5 hours'),
    ('Task B', '2 hours 30 minutes'),
    ('Task C', '1 week');

In this PostgreSQL example:

We create a table named tasks with a duration column of type INTERVAL, which allows us to store durations in various units.
We insert data into the duration column with different INTERVAL values representing the duration of each task.

RDBMS Support:

PostgreSQL: Provides native support for the INTERVAL data type and offers a wide range of interval-related functions and operators.
MySQL: Supports a similar functionality called the TIMESTAMPDIFF() function to calculate time intervals, but it does not have a dedicated INTERVAL data type.
SQL Server: Supports the TIME data type for storing time durations, but it does not have a dedicated INTERVAL data type.
Oracle Database: Provides support for intervals through the INTERVAL data type, allowing you to store and manipulate time spans.
SQLite: Supports the DATETIME data type for storing date and time values but does not have a dedicated INTERVAL data type.

Conclusion: Data Types in SQL

In this article, we explored various data types in SQL and their usage in defining the structure and characteristics of database columns. Data types are essential for maintaining data integrity, optimizing storage, and enabling specific operations on the data. Here’s a brief summary of the key points covered:

Numeric Data Types:
- INTEGER (INT), SMALLINT, BIGINT: Used for whole numbers of varying sizes.
- FLOAT, REAL, DOUBLE PRECISION: Used for approximate numeric values with different levels of precision.
Decimal and Numeric Data Types (DECIMAL, NUMERIC): Used for storing fixed-point numbers with exact precision.
Character Data Types:
- CHARACTER (CHAR): Stores fixed-length character strings, padding with spaces.
- VARCHAR: Stores variable-length character strings.
Text Data Type (TEXT): Stores large amounts of variable-length text data, suitable for long strings like articles or documents.
Date and Time Data Types:
- DATE: Stores date values (year, month, day).
- TIME: Stores time values (hours, minutes, seconds).
- DATETIME, TIMESTAMP: Stores both date and time values.
Boolean Data Type (BOOLEAN): Represents true/false or binary values.
Binary Data Types (BINARY, BLOB): Used for storing binary data, such as images or files.
Enum Data Type (ENUM): Restricts column values to a predefined list of options.
Array Data Type (ARRAY): Stores ordered collections of elements within a single column.
JSON Data Type (JSON): Stores JSON-formatted data within a column.
UUID Data Type (UUID): Stores universally unique identifiers.
Interval Data Type (INTERVAL): Represents time durations or spans.

Each data type serves a specific purpose and should be chosen based on the nature of the data being stored and the requirements of your application. Different relational database management systems (RDBMS) may have variations in data type support and syntax, so it’s important to consult the documentation of the specific RDBMS you are using.

Related Articles :

Various Data Types in SQL

INTEGER (INT)

SMALLINT

BIGINT

FLOAT

REAL

DOUBLE PRECISION

DECIMAL (DEC) or NUMERIC

CHARACTER (CHAR)

VARCHAR

TEXT

DATE

TIME

DATETIME or TIMESTAMP

BOOLEAN

BINARY

BLOB (Binary Large Object)

ENUM

ARRAY

JSON

UUID

INTERVAL

Conclusion: Data Types in SQL

Related posts:

Leave a Reply Cancel reply