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.
Table of Contents
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.