Every PostgreSQL column has a fixed type, set in CREATE TABLE. Most tables need only a handful: bigint or integer for whole numbers, numeric for money, text for strings, boolean, timestamptz for points in time, and jsonb for documents. pg_typeof() tells you the type of any value.
The main types and when to use each
| Type | Holds | Use it for |
|---|---|---|
integer (int) | Whole numbers up to about 2.1 billion, 4 bytes | Counts, quantities, small ids |
bigint | Whole numbers up to about 9.2 quintillion, 8 bytes | Primary keys, anything that can grow |
smallint | Up to 32767, 2 bytes | Rarely worth it |
numeric(p, s) | Exact decimals | Money, rates, anything summed |
real, double precision | Binary floating point | Measurements, scientific values |
text | Strings of any length | Names, emails, descriptions |
varchar(n) | Strings with a length limit | When the limit is a rule |
boolean | true, false or NULL | Flags |
date | A calendar day | Birthdays, due dates |
timestamptz | A moment in time | Created and updated times |
interval | A duration | '2 hours', '30 days' |
uuid | 128-bit identifier | Public or distributed ids |
jsonb | Parsed JSON | Data without a fixed shape |
text[], int[] | Arrays of any type | Short lists, tags |
bytea | Raw bytes | Small binary values |
The bigger choices have their own pages: timestamp vs timestamptz, enum types, jsonb and arrays. Ids (serial, identity columns, uuid) are covered under primary keys.
numeric vs double precision
numeric stores decimals exactly. real and double precision store binary fractions, so values like 0.1 are approximations and sums can drift:
The numeric sum is 0.3. The floating sum is 0.30000000000000004, and the equality test is false. Use numeric for money: numeric(10, 2) allows 10 digits in total, 2 after the point, and rounds extra decimals on insert.
Dividing an integer by an integer drops the fraction:
7 / 2 returns 3. Make one side numeric (a literal with a decimal point, or a cast) to get 3.5.
text vs varchar(n) vs char(n)
All three use the same storage, and none is faster. varchar(n) rejects longer values on insert, which helps only when the limit is a rule of your data. char(n) pads with spaces and has no use in new tables. An explicit cast to varchar(n) truncates instead of failing.
The cast returns Post. The char(5) value takes 5 bytes, but length ignores the padding and returns 2.
Boolean values and NULL
A boolean column accepts true, false, 't', 'yes', 'on', '1' and their opposites, and always returns true or false. A missing value is NULL, which is neither, so WHERE NOT active skips rows where active is NULL.
Use IS TRUE, IS NOT TRUE or COALESCE(active, false) when NULL should count as false.
Frequently Asked Questions
Is there a performance difference between text and varchar in PostgreSQL?
No. text, varchar and varchar(n) are stored the same way. varchar(n) only adds a length check, so use text unless the limit is a real rule of your data.
Should I use the money type in PostgreSQL?
Usually not. Its input and output depend on the lc_monetary setting, and a value does not record its currency. Store amounts in numeric(12,2) and keep the currency in its own column.
What is the PostgreSQL equivalent of DATETIME?
timestamptz (timestamp with time zone) for moments in time, or timestamp for a wall-clock time with no zone. PostgreSQL has no DATETIME type.
How do I see the type of a value or column in PostgreSQL?
SELECT pg_typeof(expression) returns the type of any expression, including a column. In psql, \d table_name lists every column with its type.