SQL Data Types(Numeric, String & Date)- Default Values

by | Apr 15, 2024 | SQL | 0 comments

SQL (Structured Query Language) supports various data types to represent different kinds of data. These data types define the format and constraints of the data stored in each column of a table. Here are some common SQL data types:

  1. Numeric Types:
    • INT: Integer type, typically used for whole numbers.
    • FLOAT, REAL, DOUBLE PRECISION: Floating-point types, used for decimal numbers with varying precision.
    • DECIMAL(p, s), NUMERIC(p, s): Fixed-point types, used for exact decimal numbers with precision p and scale s.
  2. Character String Types:
    • CHAR(n): Fixed-length character string with length n.
    • VARCHAR(n), VARCHAR2(n), TEXT: Variable-length character string with maximum length n.
    • NCHAR(n), NVARCHAR(n): Unicode character string types, with fixed and variable lengths, respectively.
    • CLOB: Character large object, used for large text data.
  3. Date and Time Types:
    • DATE: Date type, representing a calendar date (year, month, day).
    • TIME: Time type, representing a time of day (hour, minute, second).
    • DATETIME, TIMESTAMP: Date and time combined, representing a specific point in time.
    • INTERVAL: Interval type, representing a duration of time.
  4. Boolean Type:
    • BOOLEAN, BOOL: Boolean type, representing true or false values.
  5. Binary Data Types:
    • BINARY(n): Fixed-length binary string with length n.
    • VARBINARY(n): Variable-length binary string with maximum length n.
    • BLOB: Binary large object, used for storing large binary data.
  6. Other Types:
    • ARRAY: Array type, used to store arrays of values.
    • JSON, JSONB: JSON data type, used to store JSON documents.
    • XML: XML data type, used to store XML documents.
    • ROW: Row type, used to represent a row of values.

These are some of the common SQL data types supported by most relational database management systems (RDBMS) like MySQL, PostgreSQL, Oracle, SQL Server, SQLite, etc. The exact set of supported data types may vary slightly between different database systems. Additionally, some database systems may provide additional custom or proprietary data types specific to their implementation.

String datatype is a generic term used for different string datatypes in the database. The most used string datatypes are CHAR, which stands for character. This datatype is used to hold characters of a fixed length. VARCHAR stands for variable character. This holds characters of the variable length. 

more commonly used examples of string datatypes. TINYTEXT is used to define columns that require less than 255 characters, like short paragraphs. TEXT is used to define columns of less than 65,000 characters, like an article. MEDIUMTEXT defined columns of 16.7 million characters. For example, the text of a book. The LONGTEXT datatype stores up to four gigabytes of text data. 

In SQL, you can specify default values for columns when defining a table. Default values are used to provide a predefined value for a column if no explicit value is specified during the insertion of a new row. Here’s how you can specify default values for different SQL data types:

  1. Numeric Types:
    • For numeric types such as INT, FLOAT, DECIMAL, etc., you can specify default numeric values.
    • Example: age INT DEFAULT 18
  2. Character String Types:
    • For character string types such as CHAR, VARCHAR, TEXT, etc., you can specify default string values.
    • Example: name VARCHAR(50) DEFAULT 'John'
  3. Date and Time Types:
    • For date and time types such as DATE, TIME, DATETIME, TIMESTAMP, etc., you can specify default date and time values.
  4. Boolean Type:
    • For boolean types such as BOOLEAN, BOOL, you can specify default boolean values (TRUE or FALSE).
    • Example: is_active BOOLEAN DEFAULT TRUE
  5. Binary Data Types:
    • For binary data types such as BINARY, VARBINARY, BLOB, etc., you can specify default binary values.
    • Example: image BLOB DEFAULT NULL
  6. Other Types:
    • For other types such as ARRAY, JSON, XML, etc., you can specify default values appropriate for the respective data type.
    • Example: json_data JSON DEFAULT '{}'

Here are some important considerations when specifying default values:

  • Default values are applied only if no value is explicitly provided for the column during the insertion of a new row.
  • You can specify constants, expressions, or system functions (such as CURRENT_TIMESTAMP) as default values.
  • Some databases may have limitations on the types of default values that can be specified for certain data types.
  • You can modify the default value of a column using the ALTER TABLE statement.

Overall, default values provide a convenient way to ensure consistency and integrity in your database by automatically assigning predefined values to columns when necessary.

Written by HintsToday Team

Related Posts

Database Structures, Types of Keys

Learn about tables, fields (or attributes), records, keys and table relationships. What is database structure? A database structure is the blueprint that defines how data is arranged ,organized, stored, accessed, and managed within a database. It's the underlying...

read more

Get the latest news

Subscribe to our Newsletter


Submit a Comment

Your email address will not be published. Required fields are marked *