< Back

Owen PhillipsApril 13, 2020

Updated: April 23, 2023

Postgres Data Types: Cheat Sheet

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

  • CHAR(n)
  • VARCHAR(n)
  • TEXT

• Numeric Data Types

  • SMALLINT
  • INTEGER
  • BIGINT
  • NUMERIC
  • REAL
  • DOUBLE PRECISION
  • SMALLSERIAL
  • SERIAL
  • BIGSERIAL

• Boolean Data Types

  • BOOLEAN

• UUID Data Types

  • UUID

• JSON Data Types

  • JSON
  • JSONB

• Date and Time Data Types

  • DATE
  • TIME
  • TIMESTAMP
  • TIMESTAMPZ
  • INTERVAL

• Monetary Data Types

  • MONEY

• Binary Data types

  • BYTEA

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.

1. CHAR(n)

Variable length datatype useful for storing small strings.

Range

• Up to n characters in length.

Storage Size

• 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.

2. VARCHAR(n)

Variable length datatype useful for storing large strings

Range

• Up to n characters in length.

Storage Size

• 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.

3. TEXT

Useful for storing strings of any length.

Range

• No limit (theoretically).

Storage Size

• 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. Remaining 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.

1. SMALLINT

Useful for storing small whole numbers.

Range

• Between -32768 and 32767.

Storage Size

• 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.

2. INTEGER

Useful for storing medium whole numbers.

Range

• Between -2147483648 and 2147483647.

Storage Size

• 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

3. BIGINT

Useful for storing large whole numbers.

Range

• Between -9223372036854775808 and 9223372036854775807.

Storage Size

• 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 INT

4. NUMERIC

Useful for storing large precise, floating point numbers.

Range

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

Storage Size

• 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 where high precision is required.

5. REAL

Useful for storing small, floating point numbers when precision is not a concern.

Range

• Up to 6 decimal places.

Storage Size

• 4 bytes.

Example

ALTER TABLE recipes ADD COLUMN ingredient_grams REAL;

Tips

1. Not recommended for financial data.
2. Prone to rounding errors.
3. Also known as FLOAT and FLOAT4

6. DOUBLE PRECISION

Useful for storing large, floating point numbers when precision is not a concern.

Range

• Up to 15 decimal places.

Storage Size

• 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

7. SMALLSERIAL

Useful for storing small autoincrementing whole numbers.

Range

• From 1 to 32767

Storage Size

• 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.

8. SERIAL

Useful for storing medium autoincrementing whole numbers.

Range

• From 1 to 2147483647.

Storage Size

• 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.

9. BIGSERIAL

Useful for storing large autoincrementing whole numbers.

Range

• From 1 to 9223372036854775807.

Storage Size

• 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 types

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

1. BOOLEAN

Useful for storing a true or false values.

Range

• true OR false OR null.

Storage Size

• 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 Types

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

1. uuid

Useful for generating extremely unique ids.

Range

• 32 hyphen-seperated digits.

Storage Size

• 16 bytes.

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.

1. JSON

Useful for storing JSON in plain text.

Range

• N/A

Storage Size

• 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.

2. JSONB

Useful for storing JSON in binary format.

Range

• N/A

Storage Size

• 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 many ways to do it 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.

1. DATE

Useful for storing a date in the year/month/day format.

Range

• Low: 4713 BC
• High: 5874897 AD

Storage Size

• 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

2. TIME

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)

Storage Size

• 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

3. TIMESTAMP

Stores a timestamp and is interpreted as local date-time.

Range

• Low: 4713 BC
• High: 294276 AD

Storage Size

• 8 bytes.

Example

ALTER TABLE orders ADD COLUMN purchase_date TIMESTAMP;

Tips

1. Interpreted as local time.
2. 1 microsecond resolution

4. TIMESTAMPTZ

Stores a timestamp and is interpreted as a point on the UTC time line.

Range

• Low: 4713 BC
• High: 294276 AD

Storage Size

• 8 bytes.

Example

ALTER TABLE orders ADD COLUMN purchase_date TIMESTAMPTZ;

Tips

1. Interpreted as UTC time.
2. 1 microsecond resolution

5. INTERVAL

Useful for storing and working with periods of time.

Range

• N/A

Storage Size

16 bytes.

Example

ALTER TABLE orders ADD COLUMN subscription_length INTERVAL;

Monetary Data types

Used for storing currency amounts with fixed fractional precision.

1. MONEY

Range

• -92233720368547758.08 to +92233720368547758.07

Storage Size

• 8 bytes.

Example

ALTER TABLE blog ADD COLUMN price MONEY;

Binary Data types

Used for storing binary strings.

1. BYTEA

Range

• Varies

Storage Size

• Size of binary string plus 1 to 4 bytes.

Example

ALTER TABLE blog ADD COLUMN image BYTEA;

Tips

1. Not suited for storing extremly large amounts of binary data

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.