Postgres Data Types: Cheat Sheet

Last Updated August 21th, 2017
In this cheat sheet, we have compiled only the most essential information about each of the Postgres data types. We provide usage examples, acceptable ranges, expected disk space requirements, and quick tips so you can easily pick the right Postgres data types for your projects.
Postgres Data Types: Cheat Sheet

Character Data Types

Used for storing strings of various lengths depending on your needs. Although there is no performance difference between them, you may save disk space using VARCHAR(n) or TEXT over CHAR(n) due to the extra disk space it uses for padding.
Variable length datatype useful for storing small strings.

Range

Up to n characters in length.

Disk Space

1 byte plus length of string and padding (strings under 126 bytes).
4 bytes plus length of string and padding (strings over 126 bytes).
Maximium 1GB (Approx.).

Example

ALTER TABLE users ADD COLUMN user_name CHAR(15);

Tips

1.
Will ALWAYS store n number of characters.
2.
Remaining spaces will not be padded.
3.
Strings larger than 'n' will throw an error.
4.
Spaces do not count toward character limit but will be truncated.
Variable length datatype useful for storing large strings

Range

Up to n characters in length.

Disk Space

1 byte plus length of string (strings under 126 bytes).
4 bytes plus length of string (strings over 126 bytes).
Maximium 1GB (Approx.).

Example

ALTER TABLE users ADD COLUMN password VARCHAR(128);

Tips

1.
Will ONLY store given number of characters.
2.
Remaining spaces will not be padded.
3.
Strings larger than 'n' will throw an error.
4.
Spaces do not count toward character limit but will be truncated.
5.
Acts the same as TEXT datatype when n is not given.
Useful for storing strings of any length.

Range

No limit (theoretically).

Disk Space

1 byte plus length of string (strings under 126 bytes).
4 bytes plus length of string (strings over 126 bytes).
Maximium 1GB (Approx.).

Example

ALTER TABLE posts ADD COLUMN blog_content TEXT;

Tips

1.
Will only store given number of characters.
2.
Remaing spaces will not be padded.

Numeric Data Types

Used in Postgres for storing floating-point numbers or integers (whole numbers). Each data type has its own benefits and drawbacks depending on your needs.
Useful for storing small whole numbers.

Range

Between -32768 and 32767.

Disk Space

2 bytes

Example

ALTER TABLE users ADD COLUMN age SMALLINT;

Tips

1.
Integers outside of range will throw an error.
2.
Can not store decimal places.
3.
Also known as INT2.
Useful for storing medium whole numbers.

Range

Between -2147483648 and 2147483647.

Disk Space

4 bytes

Example

ALTER TABLE posts ADD COLUMN likes INTEGER;

Tips

1.
Integers outside of range will throw an error.
2.
Can not store decimal places.
3.
Also known as INT and INT4
Useful for storing large whole numbers.

Range

Between -9223372036854775808 and 9223372036854775807.

Disk Space

8 bytes

Example

ALTER TABLE orders ADD COLUMN epoch BIGINT;

Tips

1.
Integers outside of range will throw an error.
2.
Can not store decimal places.
3.
Also known as INT8
Useful for storing large precise, floating point numbers.

Range

Up to 131072 before decimal point.
Up to 16383 after decimal point.

Disk Space

Variable size

Example

ALTER TABLE products ADD COLUMN price NUMERIC(4, 2);

Tips

1.
Precision is user-specified.
2.
Precision must be positive.
3.
Also known as DECIMAL.
4.
Good for storing financial data.
Useful for storing small, floating point numbers when precision is not a concern.

Range

Up to 6 decimal points.

Disk Space

4 Bytes.

Example

ALTER TABLE recipes ADD COLUMN ingredient_grams REAL;

Tips

1.
Not recommended for financial values.
2.
Prone to rounding errors.
3.
Also known as FLOAT and FLOAT4
Useful for storing large, floating point numbers when precision is not a concern.

Range

Up to 15 decimal points.

Disk Space

8 Bytes.

Example

ALTER TABLE recipes ADD COLUMN ingredient_grams DOUBLE PRECISION;

Tips

1.
Not recommended for financial values.
2.
Prone to rounding errors.
3.
Also known as FLOAT8
Useful for storing small autoincrementing whole numbers.

Range

From 1 to 32767.

Disk Space

2 Bytes.

Example

ALTER TABLE players ADD COLUMN score SMALLSERIAL;

Tips

1.
Not a true data type.
2.
Only a notational convenience for creating unique identifiers.
Useful for storing medium autoincrementing whole numbers.

Range

From 1 to 2147483647.

Disk Space

4 Bytes.

Example

ALTER TABLE accounts ADD COLUMN order_id SERIAL;

Tips

1.
Not a true data type.
2.
Only a notational convenience for creating unique identifiers.
Useful for storing large autoincrementing whole numbers.

Range

From 1 to 9223372036854775807.

Disk Space

8 Bytes.

Example

ALTER TABLE orders ADD COLUMN order_id BIGSERIAL;

Tips

1.
Not a true data type.
2.
Only a notational convenience for creating unique identifiers.

Boolean Data Type

The Boolean data type in Postgres is used for storing one of three values (true, false or null).
Useful for storing a true or false value.

Range

true OR false OR null.

Disk Space

1 Byte.

Example

ALTER TABLE blog ADD COLUMN published BOOLEAN;

Tips

1.
true can also be yes, on or 1.
2.
false can also be no, off or 0
3.
Accepts a third state of unknown (null).
4.
Also known as Bool.

UUID Data Type

The UUID (Universally Unique IDentifiers) data type allows you to store UUIDs (a proposed standard by the Internet Engineering Task Force) in Postgres.
Useful for generating extremely unique ids.

Range

32 hyphen-seperated digits.

Disk Space

16 Bytes.

Example

ALTER TABLE users ADD COLUMN user_id uuid UNIQUE DEFAULT uuid_generate_v4 ();

Tips

1.
More unique than sequence generators.
2.
More disk space required than sequence generated numbers.
3.
Unlikely to be generated by anyone else using the same algorithm.
4.
uuid-ossp extension is required.

JSON Data Types

These JSON data types allow you to store JSON in either plain text or in a binary format in Postgres.
Useful for storing JSON in plain text.

Range

N/A

Disk Space

Varies

Example

ALTER TABLE users ADD COLUMN address JSON;

Tips

1.
Stores JSON 'as is' in plain text.
2.
Faster than JSONB type to input.
3.
Slower than JSONB type to process.
4.
Does not support indexing.
Useful for storing JSON in binary format.

Range

N/A

Disk Space

Varies

Example

ALTER TABLE users ADD COLUMN address JSONB;

Tips

1.
Slower than JSON type to input.
2.
Faster than JSON type to process.
3.
Support indexing.
4.
Unless there is some special requirement, JSONB is a better choice than JSON.

Date and Time Data Types

Storing date and time is an essential feature of any RDBMS and Postgres has 5 ways to do this with even more flexability than the SQL standard requires. Once date or time data is stored, Postgres has many advanced features for working with time and date data types.
Useful for storing a date in the year/month/day format.

Range

Low: 4713 BC
High: 5874897 AD

Disk Space

4 bytes

Example

ALTER TABLE users ADD COLUMN birthday DATE;

Tips

1.
Stores and inserts the date in yyyy-mm-dd format
2.
1 day resolution
The TIME data type can store time information with or without the timezone.

Range

Low: 00:00:00 (without timezone)
High: 24:00:00 (without timezone)
Low: 00:00:00+1459 (with timezone)
High: 24:00:00-1459 (with timezone)

Disk Space

8 bytes without timezone
12 bytes with timezone

Example

ALTER TABLE timezones ADD COLUMN london TIME with time zone;

Tips

1.
Columns can be created to store time with or without timezone info.
2.
May have a precision up to 6 digits
3.
1 microsecond resolution
Stores a timestamp and is interpreted as local date-time.

Range

Low: 4713 BC
High: 294276 AD

Disk Space

8 bytes

Example

ALTER TABLE orders ADD COLUMN purchase_date TIMESTAMP;

Tips

1.
Interpreted as local time.
2.
1 microsecond resolution
Stores a timestamp and is interpreted as a point on the UTC time line.

Range

Low: 4713 BC
High: 294276 AD

Disk Space

8 bytes

Example

ALTER TABLE orders ADD COLUMN purchase_date TIMESTAMPTZ;

Tips

1.
Interpreted as UTC time.
2.
1 microsecond resolution
Useful for storing and working with periods of time.

Range

N/A

Disk Space

16 bytes

Example

ALTER TABLE orders ADD COLUMN subscription_length INTERVAL;

Tips

1.
NONE

Conclusion

When working with PostgreSQL, finding the right data type for the data you're working with is essential. This cheat sheet is intended to be a handy reference for you to keep coming back to for examples, ranges, space requirements, and tips so that you can pick the right data type every time.