PostgreSQL Data types:
- talatkhatoon
- May 22
- 4 min read
INTRODUCTION:
PostgreSQL is an object relational database management system, which means it has relational capabilities and an object oriented design. Using this feature of PostgreSQL programmers can communicate with database servers using objects in their code.
WHY USE DATA TYPES:
Make queries simpler and more readable.
Solve problems elegantly in the database.
Performance and space efficiency.
It ensures that data is collected in preferred format.
Each data type has its own characteristics such as storage requirements, behavior and optimization potential, which helps in optimizing data storage ensuring data integrity and how efficient data is accessed, stored and processed.
CATEGORIES OF DATA TYPES:
Numeric Data Type
Character Data Type
Date and Time Data Type
Boolean Data Type
Enumerated Type
Other Data Types
NUMERIC DATA TYPE : These are used for integers, decimals and auto-incremental values.
INTEGER, BIGINT : For whole numbers.
Example: Age or count INTEGER
For population BIGINT
NUMERIC (Precision, Scale ): For exact decimal values.
Example: Salary NUMERIC (10, 2)
SERIAL, BIGSERIAL: Auto-incrementing integers, useful for primary key.
Example : id SERIAL PRIMARY KEY
CHARACTER DATA TYPE :
Character data types are ideal for storing textual data ranging from small length string to larger texts variables.
CHAR (n) : Its a fixed length string if the padded with space if the input is shorter. Ideal for values with consistent length.
Example : If there are two states then , state CHAR (2).
VARCHAR (n) : Its a variable length string, upto n number of characters.
Example : We can give the length of characters like last_name VARCHAR (50).
TEXT : Its an unlimited length string for long text fields.
Example : description TEXT.
DATE AND TIME DATA TYPES :
These are used for managing temporal data like dates, time and timestamp.
DATE : It stores only the dates like 2024-04-22.
TIME : It stores only the time values like 11:20:05.
TIMESTAMP : It stores both date and time like 2024-04-22 11:20:05.
BOOLEAN DATA TYPE :
It is used for true and false values.
BOOLEAN : It stores true, false or null values .
Example : is_active BOOLEAN.
ENUMERATED TYPE: (enum)
It allows us to define a data type with static, ordered set of values. It is useful for situations where a column must contain one of a limited set of predefined values enhancing data integrity and readability.
E xample values : 'small', 'medium', 'large'.
OTHER DATA TYPES : Postgresql supports advance data types for complex scenarios.
BYTEA : For storing binary data like images or files.
Example : file_data BYTEA.
JSON, JSONB : To store and query JSON objects .
Example: metadata JSONB
ARRAY : It is used for storing array of values and can be used for multi-valued fields like tax, categories or keywords.
Example : tags TEXT [ ]
RANGES : Ranges are simple but powerful variation on composite types. Always have upper/ lower / exclusive bounds.
DATA TYPES WHICH CAN BE USED INSTEAD :
Money- don't use this data type as its not modern standards as earlier it was use instead use timestamp for date and time.
float/ integer instead we can use numeric.
varchar, char instead use TEXT as its faster.
bitstring-- premature optimization.
JSON - we probably need JSONB.
TIPS FOR CHOOSING THE RIGHT DATA TYPES :
Use INTEGER for IDS and NUMERIC for financial data.
Use VARCHAR for text fields where length varies and TEXT for unlimited text.
Use JSON for flexible, hierarchial data.
For dates always prefer DATE or TIMESTAMP over storing them as TEXT.
PERFORMANCE CONSIDERATIONS BETWEEN CHAR, VARCHAR AND TEXT.
STORAGE EFFICIENCY:
CHAR : Wastes space due to padding, especially for variable-length data type.
Example :
CREATE TABLE countries (
country_code CHAR(3)
);
INSERT INTO countries(country_code) values ('US'),('IND');
STORAGE :
US is stored as 'US ' (With 1 trailing blank).
IND is stored as 'IND' (No padding needed as its exactly 3 characters).
VARCHAR :
STORAGE EFFICIENCY : Dynamically uses storage making it more efficient with no padding and it will save storage length when compared to CHAR.
CREATE TABLE employees (
first_name VARCHAR (50)
);
INSERT INTO employees(first_name) VALUES ('Aman'), ('Harshada');
STORAGE:
Aman uses only 4 characters of storage.
Harshada uses only 8 characters of storage.
TEXT :
STORAGE EFFICIENCY : No padding and ideal for storing long and structured text.
CREATE TABLE articles (
content TEXT );
INSERT INTO articles (content) VALUES ('This is a short article'),('This is a very long article that exceeds the typical length of other fields');
STORAGE :
Only the actual data is stored with no padding.
Suitable for fields like comments, description or Blogs where the length is unpredictable.
INDEXING :
Indexes for TEXT are typically larger , affecting query response.
VARCHAR (n) with a defined limit provides better control over index size.
USE CASES :
Use CHAR (N) for fixed data lengths like country code or postal codes.
Use VARCHAR (n) for predictable variable length for fields like names.
Use TEXT sparingly, for fields with no length constraint.
COMPARISION :
