PostgreSQL Data Types: Use and understand types effectively

PostgreSQL supports a plethora of different data types. We’ll cover the basic types here and provide some guidance around when to use which.

Characters

• text - Use this almost always. Supports variable length string
• varchar(n) - Use this to specify a maximum length. Use text instead as this provides no real world benefit
• char(n) - fixed length character column. Uses all character space by padding empty spaces. Can be used in special cases where string length known beforehand like country codes. Avoid using for superior alternatives. Mostly present for legacy reasons

Numbers

Integers

• smallint - −32,768 to +32,767
• integer - −2,147,483,648 to +2,147,483,647
• bigint - −9,223,372,036,854,775,808 to +9,223,372,036,854,775,807

Autoincrementing Integers

They are ideally used as primary keys in columns that need autogenerated unique values

• smallserial - 1 to 32,767
• serial - 1 to 2,147,483,647
• bigserial - 1 to 9,223,372,036,854,775,807

Floating Point Types

Decimal numbers are denoted with their precision and scale values.

• precision - denotes the total number of digits in the number, both on the left and right side of the decimal point
• scale - denotes the number of digits on the right side of the decimal point

The types are:

• numeric(precision,scale) or decimal(precision,scale)
  • they are fixed point numbers
  • up to 131072 digits before the decimal point
  • up to 16383 digits after the decimal point
  • omitting scale sets it to 0, i.e., an integer
  • omitting both precision and scale lets us store upto the limits mentioned above
• real - floating 6 decimal digits precision point
• double precision - floating 15 decimal digits precision point

Monetary Type

money

• used to store currency values
• can store values -92,233,720,368,547,758.08 to +92,233,720,368,547,758.07
• can be cast to numeric without loss of precision. Other floating point types will lose precision

Dates and Times

• timestamp - date and time. Default format is YYYY-MM-DD HH:MM:SS
• date - only date. Default format is YYYY-MM-DD
• time - only time
• interval - length of time

Booleans

Possible values of true and false

Other

These are advanced types for discussion another time

• geometric types
• network address types
• Universally Unique Identifier (UUID) type
• XML type
• JSON type