Understanding Data Types in SQL

Data types define the kind of data a column can hold, impacting storage, analysis, and operations. Choosing the correct type is fundamental for database usability and accuracy.

Core Principles

Each column in a SQL table holds only one data type.
Data types dictate how data is stored, enabling accurate analysis.
Understanding data types is crucial for building usable databases.
Data types are a programming concept applicable beyond SQL.
Choosing the right data type impacts storage efficiency and performance.
SQL supports various data types for characters, numbers, and dates/times.
PostgreSQL offers specific implementations and extensions to standard SQL types.

Key Terms

Data Dictionary: A document listing columns, their data types, and explanations of values.
CREATE TABLE: SQL statement used to define a table structure, including column data types.
char(n): Fixed-length character string, pads with spaces if shorter than n.
varchar(n): Variable-length character string, stores only entered characters.
text: Variable-length character string with a very large maximum length (PostgreSQL specific).
integer: Data type for whole numbers (positive, negative, or zero).
bigint: Integer type capable of holding very large numbers.
numeric(precision, scale): Fixed-point number type specifying total digits and digits after the decimal.
real: Floating-point type with limited decimal precision.
double precision: Floating-point type with higher decimal precision.
timestamp: Data type storing both date and time.
date: Data type storing only the date.
time: Data type storing only the time.
interval: Data type representing a duration or time span.
CAST(): SQL function to convert a value from one data type to another.

Real World Examples

Storing US state postal abbreviations: Use `char(2)` for fixed-length, predictable data.
Storing product names or descriptions: Use `varchar(n)` with an appropriate maximum length to save space.
Storing monetary values: Use `numeric` or `decimal` for exact calculations, avoiding floating-point issues.
Storing event dates and times with time zone information: Use `timestamp with time zone` for accurate global event tracking.
Calculating future follow-up dates: Use `interval` to add or subtract durations from date/timestamp columns.

Timeline

Ancient Times: Early forms of data recording and categorization.
1970s: Development of relational database models and SQL.
1980s: Standardization of SQL, defining core data types like `INTEGER`, `VARCHAR`, `DATE`.
1990s - Present: Evolution of SQL with more complex data types (e.g., `JSON`, `XML`, `UUID`) and database-specific extensions (e.g., PostgreSQL's `text`, `bigserial`).