Care Tech Team

Database data types are classifications of the types of data that can be stored in a database. Understanding the available data types is crucial for structuring data properly, optimizing performance, and ensuring data integrity. While the exact set of data types can vary slightly depending on the database system (like MySQL, PostgreSQL, Oracle, etc.), here are 100 common database data types, organized into different categories:

Numeric Data Types

1. INT (or INTEGER) – Integer values without decimals.
2. SMALLINT – Small integer values.
3. BIGINT – Large integer values.
4. TINYINT – Very small integer values.
5. DECIMAL – Fixed-point numbers with a defined scale and precision.
6. NUMERIC – Similar to DECIMAL, stores exact numeric data.
7. FLOAT – Floating-point numbers (approximate).
8. REAL – A smaller floating-point number.
9. DOUBLE – Double-precision floating-point numbers.
10. MONEY – Monetary values, often formatted for financial data.
11. SERIAL – Auto-incrementing integer values, often used for primary keys (PostgreSQL).
12. BIGSERIAL – Similar to SERIAL but with a larger range (PostgreSQL).
13. BIT – Stores binary data, typically 1 or 0.

Character and String Data Types

14. CHAR – Fixed-length character string.
15. VARCHAR – Variable-length character string.
16. TEXT – Large text data.
17. CHARACTER – Equivalent to CHAR.
18. VARCHAR2 – Variable-length character string (Oracle-specific).
19. CLOB – Character large object, stores large amounts of text data.
20. BLOB – Binary large object, stores large binary data.
21. ENUM – A predefined set of string values.
22. SET – A set of values (usually for flags or choices).
23. NCHAR – Fixed-length string for Unicode characters.
24. NVARCHAR – Variable-length string for Unicode characters.
25. NTEXT – Unicode text data.

Date and Time Data Types

26. DATE – Stores date (year, month, day).
27. TIME – Stores time (hours, minutes, seconds).
28. DATETIME – Stores date and time.
29. TIMESTAMP – Similar to DATETIME, often includes fractional seconds.
30. YEAR – Stores year data (e.g., 2025).
31. SMALLDATETIME – A smaller range of DATETIME.
32. TIME WITH TIME ZONE – Time with time zone information.
33. TIMESTAMP WITH TIME ZONE – Timestamp with time zone information.
34. DATE/TIME WITHOUT TIME ZONE – Only date or time without time zone info.
35. INTERVAL – Represents a duration of time (can store differences between two dates).

Binary Data Types

36. BINARY – Fixed-length binary data.
37. VARBINARY – Variable-length binary data.
38. IMAGE – Stores binary data, usually images or media files.
39. BYTEA – Stores binary data (PostgreSQL).
40. UUID – Universally Unique Identifier, often used for global unique keys.

Boolean Data Type

41. BOOLEAN – Stores Boolean values (TRUE, FALSE, or NULL).

Geospatial Data Types

42. POINT – Stores a point in 2D space.
43. LINESTRING – Stores a series of points, forming a line.
44. POLYGON – Stores a polygon shape.
45. MULTIPOINT – Stores multiple points.
46. MULTILINESTRING – Stores multiple lines.
47. MULTIPOLYGON – Stores multiple polygons.
48. GEOMETRY – Generic spatial data type (can store any spatial data).
49. GEOGRAPHY – Similar to GEOMETRY, but with more advanced features for handling geographic data.
50. GEOMETRYCOLLECTION – A collection of geometric objects.

JSON and XML Data Types

51. JSON – Stores JSON (JavaScript Object Notation) formatted data.
52. JSONB – Stores JSON data in a binary format (PostgreSQL).
53. XML – Stores XML (eXtensible Markup Language) data.
54. JSONPATH – Stores a reference to a particular path within JSON data.

Array Data Types

55. ARRAY – Stores a collection of values (e.g., an array of integers).
56. HSTORE – Stores key-value pairs (PostgreSQL).

Network and IP Data Types

57. INET – Stores IP addresses (IPv4 or IPv6).
58. CIDR – Stores an IP network range.
59. MACADDR – Stores MAC addresses.

Interval and Duration Data Types

60. INTERVAL – Represents the difference between two dates or times (e.g., 10 days, 5 hours).
61. TIMESTAMP WITH INTERVAL – Timestamp including an interval.

Monetary Data Types

62. MONEY – A currency value (often with 2 decimal places).
63. DECIMAL (also used for currency) – Numeric data type with fixed precision.
64. CURRENCY – A representation of currency values (varies by DBMS).

Specialized Numeric Types

65. NUMBER – Numeric type, specific to Oracle databases.
66. FLOAT8 – 8-byte floating-point value (PostgreSQL).
67. NUMERIC – Fixed-precision numeric value.

Hash and Checksum Data Types

68. HASH – Stores hash values generated from a string.
69. CHECKSUM – Stores a checksum value to ensure data integrity.

Composite and User-defined Data Types

70. ROW – A composite data type that stores a record.
71. RECORD – A composite data type used in PostgreSQL.
72. USER-DEFINED TYPES (UDT) – Custom data types defined by the user (can represent complex structures).
73. ARRAY of USER-DEFINED TYPES – Arrays of complex user-defined data types.
74. UUIDARRAY – Array of UUID values.

Timezone and Offset Data Types

75. TIMEZONE – A time data type that includes a timezone.
76. TIMESTAMP WITH TIMEZONE OFFSET – Stores timestamp with a timezone offset.
77. DATETIME WITH OFFSET – Stores date and time with timezone offset.

File and Large Object Data Types

78. FILE – Stores file data (e.g., file paths).
79. BLOB (Binary Large Object) – Stores large binary data like images, video, and files.
80. CLOB (Character Large Object) – Stores large text data.
81. TEXT – Stores large text or unstructured data.
82. VARBINARY – Variable-length binary data used to store files.

Date and Time Precision Types

83. DATE WITH PRECISION – Date data type that includes time precision (milliseconds, microseconds, etc.).
84. DATETIME(6) – DATETIME type with microsecond precision.
85. TIME(3) – TIME type with millisecond precision.

Multi-dimensional Data Types

86. MATRIX – Stores a matrix (multidimensional array) of values.
87. VECTOR – Stores a vector (one-dimensional array) of numeric values.

Enumeration and Set Data Types

88. ENUM – A set of predefined values (useful for categorical data).
89. SET – Similar to ENUM, but stores multiple values (e.g., flags).

Full-Text and Document Data Types

90. TEXTSEARCH – Stores text data designed for full-text search operations.
91. DOCUMENT – A data type designed for document storage and retrieval (commonly used with document databases).
92. FULLTEXT – Stores indexed text for fast full-text search.
93. XMLDOCUMENT – Stores XML documents for structured data.

Large Integer Data Types

94. BIGSERIAL – Large auto-incrementing integer values (PostgreSQL).
95. BIGINT – Integer with a larger range.
96. LONG – A very large number (used in some databases like Oracle).

Geospatial Indexing Types

97. GEOMETRY INDEX – An index for spatial (geometry) data types.
98. R-Tree – An index used for spatial queries (like polygons).
99. GiST – Generalized Search Tree index for various complex data types.

Specialized Boolean Data Types

100. BOOLEAN ARRAY – Array of Boolean values (TRUE/FALSE).

Conclusion

These are just some of the database data types available in popular database management systems like MySQL, PostgreSQL, Oracle, and SQL Server. Different databases have different data type implementations and capabilities, but this list should give you a good understanding of the variety of types that can be used for managing and optimizing data. Properly selecting the right data type for each piece of data is crucial for performance, storage efficiency, and ensuring the integrity of your database.