Each column has one data type. The type decides which values the column accepts. Choose it when you create the table.
Numbers
| Type | Holds | Example |
|---|---|---|
integer | whole numbers from about −2.1 billion to +2.1 billion | 42 |
bigint | whole numbers up to about 9.2 × 10¹⁸ | 7055550142 |
numeric(p,s) | exact decimals: p digits in total, s of them after the point | numeric(8,2): up to 999999.99 |
real | approximate decimals, with small rounding errors | 0.30000001 |
numeric(8,2)rounds12.345to12.35. It refuses1234567.5: that needs 7 digits before the point, and 8 − 2 leaves 6.- Use
numericfor money. Userealonly for measurements where a tiny error does not matter.
Text
| Type | Holds | Example |
|---|---|---|
text | text of any length | Kraken Plush |
varchar(n) | text of at most n characters | varchar(40) |
char(n) | text of exactly n characters; a shorter value gets spaces at the end | char(2): CA |
Text and codes often look like numbers. Use a text type when the value is a code and not an amount: phone numbers, postal codes, product codes. A number column removes a leading 0 and refuses + and spaces.
Yes or no, dates and times
| Type | Holds | Example |
|---|---|---|
boolean | true or false | true |
date | a day | 2025-03-14 |
timestamp | a day and a time of day | 2025-03-14 09:30:00 |
Choose a type
Whole number, decimal, text, yes/no or date?1
Exact or approximate? How many digits?2
The smallest type that holds every value that can come3
The same types in MySQL:
| PostgreSQL | MySQL |
|---|---|
integer | INT |
bigint | BIGINT |
numeric(8,2) | DECIMAL(8,2) |
real | FLOAT |
varchar(40) | VARCHAR(40) |
char(2) | CHAR(2) |
boolean | TINYINT(1) |
timestamp | DATETIME |
PostgreSQL also accepts int and decimal as other names for integer and numeric.