PostgreSQL Data Types
PostgreSQL is an excellent industrial-grade open-source RDBMS that is highly reliable and versatile in its performance. In the area of PostgreSQL data storage and processing, it has won appreciations due to its diversified support for PostgreSQL data types. The structured query language has many types of PostgreSQL data which is the most unique and significant characteristic that makes it the best tool for a database. This way users can do the most effective data operation with the most powerful and the most flexible tool for data manipulation which is zlib.
PostgreSQL offers a wider range of PostgreSQL data types from the more basic to the complex ones. The main cause of its flexibility regarding the connection existing in PostgreSQL is the different PostgreSQL data types proposes.
If you are looking for a detailed study on those important PostgreSQL data types, or if you are excited about the different features and using PostgreSQL data types in different scenarios, you are in the right place. This guide will get you started with the different PostgreSQL data types, such as numeric, string, date/time, binary, geometric, network address, JSON, and custom PostgreSQL data types.
Once you finish these studies, you can give real-world examples of each PostgreSQL data type above, which is demonstrated by the real-world example to teach it. Now, PostgreSQL data types! Let’s explain how they are present in databases of PostgreSQL.
1. Numeric Data Types
- PostgreSQL comes with diverse numeric data types, whereby both of them can be observed: the one for handling exact information and the other one for handling floating-point information.
1.1 Integer Types
- smallint: It is a 2-byte long and is signed with a range from -32,768 to 32,767.
CREATE TABLE employees (
employee_id smallint,
age smallint
);
INSERT INTO employees (employee_id, age)
VALUES (1, 29), (2, 35);
- integer (or int): It is a 4-byte long and is signed with a range from -2,147,483,648 to 2,147,483,647.
CREATE TABLE products (
product_id integer,
quantity integer
);
INSERT INTO products (product_id, quantity)
VALUES (1001, 5000), (1002, 3500);
- bigint: It is 8-byte long and has a range from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
CREATE TABLE transactions (
transaction_id bigint,
amount bigint
);
INSERT INTO transactions (transaction_id, amount)
VALUES (123456789012345, 5000000);
1.2 Floating-Point Types
- real: A 4-byte single-precision floating-point number. It can cause the range of values around -3.4E+38 to 3.4E+38.
CREATE TABLE measurements (
sensor_id integer,
temperature real
);
INSERT INTO measurements (sensor_id, temperature)
VALUES (1, 22.45), (2, 19.88);
1.3 Exact Numeric Types
- Exact numeric or decimal:
- A user-defined precision, exact numeric type.
- It permits you to set the amount of numbers after the decimal point (precision) and the number on the left (scale) as part of the notation.
- The mostly used financial or rounding other errors that are unacceptable are the main applications of it.
CREATE TABLE financial_transactions (
transaction_id serial PRIMARY KEY,
amount numeric(10, 2) — Maximum of 10 digits, 2 after decimal
);
INSERT INTO financial_transactions (amount)
VALUES (1234.56), (7890.12);
1.4 Serial Types
- serial: An auto-incrementing integer, used for creating unique identifiers (typically as primary keys).
CREATE TABLE users (
user_id serial PRIMARY KEY,
username varchar(50)
);
INSERT INTO users (username)
VALUES (‘john_doe’), (‘alice_smith’);
bigserial: An auto-incrementing 8-byte integer.
CREATE TABLE orders (
order_id bigserial PRIMARY KEY,
order_date timestamp
);
INSERT INTO orders (order_date)
VALUES (‘2024-12-31 12:00:00’);
2. Character Types
PostgreSQL supports several types for storing characters of which fixed-length, variable-length, and non-bounded text fields are included.
2.1 Character Types
- char(n): A fixed-length character. The string will be filled with spaces if it is shorter than the defined length.
CREATE TABLE products (
product_code char(10), – Fixed length of 10 characters
product_name varchar(100)
);
INSERT INTO products (product_code, product_name)
VALUES (‘A123456789’, ‘Laptop’), (‘B987654321’, ‘Smartphone’);
- varchar(n): A variable-length character type with a maximum length n. It functions are only as many spaces as are required.
CREATE TABLE students (
student_id integer PRIMARY KEY,
first_name varchar(50), – Variable length
last_name varchar(50)
);
INSERT INTO students (student_id, first_name, last_name)
VALUES (1, ‘John’, ‘Doe’), (2, ‘Alice’, ‘Smith’);
- text: A variable-length character type with no upper limit. Great for holding long strings like descriptions or content.
CREATE TABLE blog_posts (
post_id serial PRIMARY KEY,
title varchar(200),
content text
);
INSERT INTO blog_posts (title, content)
VALUES (‘Post 1’, ‘This is a blog post with detailed information about data types in PostgreSQL.’);
3. Date/Time Data Types
PostgreSQL provides a wide range of data types regarding date and time that can be used in time-based tracking and manipulation.
3.1 Date Types
- date: Stores only the date (year, month, day).
CREATE TABLE events (
event_id serial PRIMARY KEY,
event_name varchar(100),
event_date date
);
INSERT INTO events (event_name, event_date)
VALUES (‘Conference’, ‘2024-09-15’);
- timestamp: Stores both date and time (without timezone).
CREATE TABLE appointments (
appointment_id serial PRIMARY KEY,
client_name varchar(100),
appointment_time timestamp
);
INSERT INTO appointments (client_name, appointment_time)
VALUES (‘John Doe’, ‘2024-01-01 09:30:00’);
- timestamptz: Similar to timestamp, but also stores the time zone information.
CREATE TABLE global_meetings (
meeting_id serial PRIMARY KEY,
meeting_time timestamptz
);
INSERT INTO global_meetings (meeting_time)
VALUES (‘2024-12-31 14:00:00+02’); — Time in UTC+2
3.2 Time Types
- time: Stores time without date information.
CREATE TABLE shifts (
shift_id serial PRIMARY KEY,
start_time time,
end_time time
);
INSERT INTO shifts (start_time, end_time)
VALUES (’08:00
start_time timetz
);
INSERT INTO work_shifts (start_time)
VALUES (’09:00:00+05:00′);
3.3 Interval Type
- interval: It is used to designate timeframes or intervals. Days, hours, minutes, and seconds can be represented by this.
CREATE TABLE project_deadlines (
project_id serial PRIMARY KEY,
deadline timestamp,
time_remaining interval
);
INSERT INTO project_deadlines (deadline, time_remaining)
VALUES (‘2024-12-31 17:00:00’, ’10
4. Boolean Type
- The boolean type holds truth values, which are TRUE, FALSE, or NULL (unknown).
- Flags, toggles, and binary state indicators are especially needed by this type.
CREATE TABLE tasks (
task_id serial PRIMARY KEY,
task_name varchar(100),
is_completed boolean
);
INSERT INTO tasks (task_name, is_completed)
VALUES (‘Task 1’, true), (‘Task 2’, false);
5. Binary Data Types
- PostgreSQL offers several binary data types that allow storage of binary data like images, files, or other non-textual data.
5.1 bytea
- Type bytea is the one that can store the binary form of the file. It can save files, the picture’s information, and other binary objects.
CREATE TABLE files (
file_id serial PRIMARY KEY,
file_name varchar(255),
file_data bytea
);
— Insert binary data (e.g., a file) into the table
— Example assumes you have the file in binary form;
INSERT INTO files (file_name, file_data)
VALUES (‘image.png’, pg_read_binary_file(‘/path/to/image.png’));
6. JSON and JSONB Data Types
6.1 JSON
- PostgreSQL has a data type called “json” that allows users to store data in the format of text.
- This method enables you to store data that is partly structured with key-value pairs.
CREATE TABLE users (
user_id serial PRIMARY KEY,
user_data json
);
INSERT INTO users (user_data)
VALUES (‘{“name”: “John”, “age”: 30, “city”: “New York”}’);
6.2 JSONB
- The jsonb data type stores JSON data in its binary form, which makes indexing and querying faster and also saves space.
- However, it is a little costlier in terms of storage space than the json.
CREATE TABLE products (
product_id serial PRIMARY KEY,
product_info jsonb
);
INSERT INTO products (product_info)
VALUES (‘{“name”: “Laptop”, “price”: 1200.99, “in_stock”: true}’);
7. Geometric Data Types
Being a geodesic that can store spatial data like points, lines and rectangles, PostgreSQL ends up being usually found in geographical information systems (GIS) or in scientific applications.
- point: A 2D point.
CREATE TABLE locations (
location_id serial PRIMARY KEY,
coordinates point
);
INSERT INTO locations (coordinates)
VALUES (‘(10, 20)’);
- line: A straight line defined by an equation.
CREATE TABLE roads (
road_id serial PRIMARY KEY,
road_path line
);
INSERT INTO roads (road_path)
VALUES (‘[(0,0), (5,5)]’);
- box: A rectangular box defined by two corners.
CREATE TABLE areas (
area_id serial PRIMARY KEY,
region box
);
INSERT INTO areas (region)
VALUES (‘(0,0), (5,5)’);
8. Network Address Types
PostgreSQL uses various data types to store data where the data is essential to network. For instance, it can store IP addresses and other network-related data.
8.1 inet
- The inet type is used to store IP addresses, either IPv4 or IPv6.
CREATE TABLE servers (
server_id serial PRIMARY KEY,
IP_address inet
);
INSERT INTO servers (IP_address)
VALUES (‘192.168.1.1’);
8.2 cidr
- The cidr type stores IP networks, which use a CIDR prefix that specifies the subnet mask.
CREATE TABLE networks (
network_id serial PRIMARY KEY,
network_address cidr
);
INSERT INTO networks (network_address)
VALUES (‘192.168.1.0/24’);