Menu

PostgreSQL Data Types: Which Type to Use

The PostgreSQL data types you will use for almost every column: integers, numeric, text, boolean, timestamptz, uuid, jsonb, arrays and enums, with when to pick each.

This page includes runnable editors. Edit, run, and see output instantly.

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

TypeHoldsUse it for
integer (int)Whole numbers up to about 2.1 billion, 4 bytesCounts, quantities, small ids
bigintWhole numbers up to about 9.2 quintillion, 8 bytesPrimary keys, anything that can grow
smallintUp to 32767, 2 bytesRarely worth it
numeric(p, s)Exact decimalsMoney, rates, anything summed
real, double precisionBinary floating pointMeasurements, scientific values
textStrings of any lengthNames, emails, descriptions
varchar(n)Strings with a length limitWhen the limit is a rule
booleantrue, false or NULLFlags
dateA calendar dayBirthdays, due dates
timestamptzA moment in timeCreated and updated times
intervalA duration'2 hours', '30 days'
uuid128-bit identifierPublic or distributed ids
jsonbParsed JSONData without a fixed shape
text[], int[]Arrays of any typeShort lists, tags
byteaRaw bytesSmall 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.

Coddy programming languages illustration

Learn to code with Coddy

Get started