Data Types

Numeric Types

• int
• Whole number
• Max 4.294.967.295 (4 billions)
• decimal
• DECIMAL(5,2): max 99999.99
• Calculations are exact, it's a fixed-point type!
• Decimal is always the best choice if precision matters.
• float
• Calculations are approximate. Numbers are real!
• Store larger numbers using less space
• Less precise than decimal
• double
• Basically the same as float but more precise! And double memory needed.
• tinyint
• smallint
• mediumint
• bigint
• numeric
• bit

String Types

• varchar
• Between 1 and 255 characters
• varchar (100): 100 is the max length
• char
• Char has a fixed length!
• Var(10) will always have exactly 10 chars
• Good for fixed length text: E.g. CA, NY, MG, Y/N, M/F
• binary
• varbinary
• blob
• tinyblob
• medium blob
• longblob
• text
• tinytext
• mediumtext
• longtext
• enum

Date Types

• date -YYYY-MM-DD
• time
• HH:MM:SS
• datetime
• YYYY-MM-DD HH:MM:SS
• timestamp
• Same as datetime, but...
• from 1970 until 2038
• Only 4 bytes (compared to 8 in datetime)
• It's most used for info about created_at, updated_at ...
• NEVER used for birth dates
• year

Character

• Fixed character: char
• Fixed-length character strings: char(n)
• Spaces will be added to remaining space
• Values longer than the length retrive error
• Variable-length character strings: varchar(n)
• Store up to n characters
• No spaces if value is shorter than the length
• Number
• Integer
• smallint: 2-byte int. (-32768; 32767)
• int: 4-byte int. (-214783648; 214783647)
• serial: same as int, but already populated into the column
  • Similar to AUTO_INCREMENT in DBMS
• Floating-point numbers
• float(n): precision up to n, maximum of 8 bytes
• real / float8: double-precision. 8-byte number
• numeric / numeric(p,s): p digits, s numbers after decimal point
• numeric(p,): the exact number
• Temporal
• date
• time
• timestamp: data and time
• interval: difference in timestamps
• timestamp: timestamp and timezone data
• Special Types
• Array