Every column must be declared to have a datatype, also called a domain.
This tells the database system how to represent the data internally.
Optional elements are in square [] brackets, comments follow --.
| Description | SQL | MS Access | MySQL | PostgreSQL |
|---|---|---|---|---|
| Numeric Data | ||||
| binary integer 1 byte | none | byte | tinyint [unsigned] | none |
| binary integer 2 bytes | smallint | number, integer | smallint [unsigned] | smallint, int2 |
| binary integer 4 bytes | integer int |
number - long integer | int [unsigned] integer [unsigned] |
integer, int, int4 |
| binary integer 8 bytes | bigint [unsigned] | bigint, int8 | ||
| floating-point 4 bytes |
float[(p)] real |
number, single | float | real, float4 |
| floating-point 8 bytes |
double precision | number, double | double, real double precision |
double precision, float, float8 |
| decimal | decimal[(p[, s])]
dec[(p[, s])] numeric[(p[, s])] |
number, decimal(p, s) | decimal(p, s) | numeric(p,s), decimal(p,s) |
| Character Data | ||||
| character fixed length |
char(n) character(n) |
see variable | char(n) | char(n) character(n) |
| character variable length |
varchar(n), character varying(n) | text(n) -- n<256 | varchar(n) -- n: 0..255 |
varchar(n), character varying(n) |
| CLOBs = Character large objects |
memo -- up to 65,587 bytes | tinytext -- 0..255 text -- 0..65535 mediumtext LONGTEXT |
text | |
| Time Data | ||||
| Date | date | date | date | date |
| Time | time | time | time | time |
| Date and Time | timestamp | timestamp, datetime, year | timestamp | |
| True/false values and bit strings | ||||
| True/false | boolean, bool | ? | ? | boolean, bool |
| Bit strings | bit(n) bit varying(n) |
? | ? | bit(n) bit varying(n) |
| Binary Data | ||||
| BLOBs = Binary large objects |
tinyblob -- 0..255 blob -- 0..65535 mediumblob longblob |
|||
| Autoincrementing | ||||
| Auto inc | none | auto_increment | serial | |
| Other | ||||
| Other | enum(value,...)
set(value,...) |
|||