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).
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).
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).
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
Example
ALTER TABLE users ADD COLUMN age SMALLINT;
Tips
1.
Integers outside of range will throw an error.
2.
Can not store decimal places.
Useful for storing medium whole numbers.
Range
Between -2147483648 and 2147483647.
Disk Space
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
Example
ALTER TABLE orders ADD COLUMN epoch BIGINT;
Tips
1.
Integers outside of range will throw an error.
2.
Can not store decimal places.
Useful for storing large precise, floating point numbers.
Range
Up to 131072 before decimal point.
Up to 16383 after decimal point.
Disk Space
Example
ALTER TABLE products ADD COLUMN price NUMERIC(4, 2);
Tips
1.
Precision is user-specified.
2.
Precision must be positive.
4.
Good for storing financial data.
Useful for storing small, floating point numbers when precision is not a concern.
Range
Disk Space
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
Disk Space
Example
ALTER TABLE recipes ADD COLUMN ingredient_grams DOUBLE PRECISION;
Tips
1.
Not recommended for financial values.
2.
Prone to rounding errors.
Useful for storing small autoincrementing whole numbers.
Range
Disk Space
Example
ALTER TABLE players ADD COLUMN score SMALLSERIAL;
Tips
2.
Only a notational convenience for creating unique identifiers.
Useful for storing medium autoincrementing whole numbers.
Range
Disk Space
Example
ALTER TABLE accounts ADD COLUMN order_id SERIAL;
Tips
2.
Only a notational convenience for creating unique identifiers.
Useful for storing large autoincrementing whole numbers.
Range
From 1 to 9223372036854775807.
Disk Space
Example
ALTER TABLE orders ADD COLUMN order_id BIGSERIAL;
Tips
2.
Only a notational convenience for creating unique identifiers.
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
Disk Space
Example
ALTER TABLE users ADD COLUMN birthday DATE;
Tips
1.
Stores and inserts the date in yyyy-mm-dd format
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
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
Disk Space
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
Disk Space
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
Disk Space
Example
ALTER TABLE orders ADD COLUMN subscription_length INTERVAL;
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.