PostgreSQL Data Types: Byte, Numeric, Character, Binary
PostgreSQL offers a rich set of native data types for users. Users can add new types with the help of CREATE TYPE command. It also makes queries simpler and more readable.
Data Types in PostgreSQL
PostgreSQL supports the following data types:
- Text Types
- Numeric Types
- Dates and Times
- XML
- JSON
- Boolean
- Bits
- Binary Data
- Network
- Arrays
- Create your Data Type
- Boolean
- Temporal
- UUID
- Array
- JSON
- Special Data types for storing a network address and geometric data.
Let’s study PostgreSQL data types in detail
Character Datatypes
PostgreSQL supports character data types for storing text values. PostgreSQL builds character data types off of the same internal structures. PostgreSQL offers three character data types: CHAR(n), VARCHAR(n), and TEXT.
Name | Description |
---|---|
varchar(n) | Allows you to declare variable-length with a limit |
Char(n) | Fixed-length, blank padded |
Text | Use can use this data type to declare a variable with unlimited length |
Numeric Datatypes
PostgreSQL supports two distinct types of numbers:
- Integers
- Floating-point numbers
Name | Store size | Range |
---|---|---|
smallint | 2 bytes | -32768 to +32767 |
integer | 4 bytes | -2147483648 to +2147483647 |
bigint | 8 bytes | -9223372036854775808 to 9223372036854775807 |
decimal | variable | If you declared it as decimal datatype ranges from 131072 digits before the decimal point to 16383 digits after the decimal point |
numeric | variable | If you declare it as the number, you can include number up to 131072 digits before the decimal point to 16383 digits after the decimal point |
real | 4 bytes | 6 decimal digits precision |
double | 8 bytes | 15 decimal digits precision |
Binary Data Types
A binary string is a sequence of octets or bytes. Binary Postgres Data Types are divided in two ways.
- Binary strings allow storing odds of value zero
- Non- printable octets
Character strings not allow zero octets and also disallows any other octet values and sequences which are invalid as per the database’s character set encoding rules.
Name | Storage size | Description |
---|---|---|
Byte | 1 to 4 bytes plus the size of the binary string | Variable-length binary string |
Network Address Type
Many applications store network information like IP addresses of users or sensors. PostgreSQL has three native types which help you to optimize the network data.
Name | Size | Description |
---|---|---|
cider | 7 or 19 bytes | IPV4 and IPv6 networks |
Inet | 7 or 19 bytes | IPV4 and IPV5 host and networks |
macaddr | 6 bytes | MAC addresses |
Using Network Address Types has following advantages
- Storage Space Saving
- Input error checking
- Functions like searching data by subnet
Text Search Type
PostgreSQL provides two data types which are designed to support full-text search. Full-text search is searching through a collection of natural-language documents to search those that best match a query.
- Tsvector text search PostgreSQL variable types represent a document in a form optimized for text search
- The query type text search stores the keywords that need to be searched
Date/Time Datatypes
PostgreSQL timestamp offers microsecond precision instead of second precision. Moreover, you also have the option of storing with timezone or without. PostgreSQL will convert timestamp with timezone to UTC on input and store it.
Date and time input is accepted in various formats, including traditional Postgres, ISO 8601. SQL-compatible etc.
PostgreSQL supports Day / Month / Year ordering. Formats supported are DMY, MDY, YMD
Temporal Data Types
Name | Size | Range | Resolution |
---|---|---|---|
Timestamp without timezone | 8 bytes | 4713 BC to 294276 AD | 1microsecond/14 digits |
Timestamp with timezone | 8 bytes | 4713 BC to 294276 AD | 1microsecond/14 digits |
date | 4 bytes | 4713 BC to 294276 AD | One day |
Time without timezone | 8 bytes | 00:00:00 to 24:00:00 | 1microsecond/14 digits |
Time with timezone | 12 bytes | 00:00:00 + 1459 to 24:00:00-1459 | 1microsecond/14 digits |
Interval | 12 bytes | -178000000 to 178000000 years | 1microsecond/14 digits |
Examples
Input | Description |
---|---|
2025-09-07 | ISO 8601, September 7 with any date style (recommended format) |
September 7, 2025 | September 7 with any date style |
9/7/2025 | September 7 with MDY, July 9 with DMY |
9/7/25 | September 7, 2025, with MDY |
2025-Sep-7 | September 7 with any date style |
Sep-7-2018 | September 7 with any date style |
7-Sep-25 | September 7, 2025, with YMD |
20250907 | ISO 8601,7 Sep 20225 in any mode |
2025.250 | year and day of the year, in this case, Sep 7, 2025 |
J25250 | Julian date |
Time/ Time with Time Zone Input
Input | Description |
---|---|
11:19:38.507 11:19:38 11:19 111938 |
ISO 8601 |
11:19 AM | Same as 11:19 |
11:19 PM | same as 23:19 |
23:19-3 23:19-03:00 231900-03 |
ISO 8601, same as 11:19 PM EST |
23:19 EST | time zone specified as EST, same as 11:19 PM EST |
Boolean Type
A Boolean data type can hold
- True
- False
- null
values.
You use a bool or boolean keyword to declare a column with the Boolean data type.
When you insert values into a boolean column, Postgre converts values like
- Yes
- y
- 1
- t
- true
into 1.
While values like
- No
- N
- 0
- F
- False
are converted to 0
While selecting data, the values are again converted back to yes, true, y, etc.
Geometric Data Types
Geometric data types represent two-dimensional spatial objects. They help perform operations like rotations, scaling, translation, etc.
Name | Storage Size | Representation | Description |
---|---|---|---|
Point | 16 bytes | Point on a plane | (x,y) |
Line | 32 bytes | Infinite line | ((xl.yl ).(x2.y2)) |
Lseg | 32 bytes | Finite line segment | ((xl.yl ).(x2.y2)) |
Box | 32 bytes | Rectangular Box | ((xl.yl ).(x2.y2)) |
Path | 16n + 16n bytes | Close and Open Path | ((xl.yl),…) |
Polygon | 40 + 16n bytes | Polygon | [(xl.yl)….] |
Circle | 24 bytes | Circle | <(x.y).r> (center point and radius) |
Enumerated Types
Enumerated PostgreSQL data type is useful for representing rarely changing information such as country code or branch id. The Enumerated data type is represented in a table with foreign keys to ensure data integrity.
Example
Hair color is fairly static in a demographic database
CREATE TYPE hair_color AS ENUM ('brown','black','red','grey','blond')
Range Type
Many business applications require data in ranges. Typically, two columns (example: start date, end date) are defined to deal with ranges. This is both inefficient and difficult to maintain.
Postgre has built range types as follows
- int4range — Display range of integer
- int8range — Display range of bigint
- numrange — Shows the numeric range
- tstrange — Helps you to display timestamp without time zone
- strange — Allows you to display timestamp with time zone
- date range — Range of date
UUID type
Universally Unique Identifies (UUID) is a 128-bit quantity which is generated by an algorithm. It is very unlikely that the same identifier will be generated by another person in the world using the same algorithm. That’s why for the distributed systems, these identifiers are an ideal choice as it offers uniqueness within a single database. A UUID is written as a group of lower-case hexadecimal digits, with various groups separated by hyphens.
PostgreSQL has a native UUID data type which consumes 16 bytes of storage.. UUID is an ideal Data type for primary keys.
Example
d5f28c97-b962-43be-9cf8-ca1632182e8e
Postgre also accepts alternative forms of UUID inputs like all capital case, no hyphens, braces, etc.
XML type
PostgreSQL allows you to store XML data in a data type, but it is nothing more than an extension to a text data type. But the advantage is that it checks that the input XML is well-formed.
Example
XMLPARSE (DOCUMENT '<?xml version="1.0"?><tutorial><chapter>Data Type</chapter><chapter>...</chapter></tutorial>')
JSON Type
To store JSON data PostgreSQL offers 2 data types
- JSON
- JSONB
json | Jsonb |
---|---|
A simple extension of a text data type with JSON validation | A binary representation of the JSON data |
Insert is fast but data retrieval is relatively slow. | Insert is slow but select (data retrieval is fast) |
Saves inputted data exactly the way it is including whitespace. | Supports indexing. May optimize the whitespace to make retrieval faster. |
Reprocessing on data retrieval | No reprocessing required on data retrieval |
Most widely used JSON data type used us jsonb unless there is some specialized need to use JSON data type.
Example
CREATE TABLE employee ( id integer NOT NULL, age integer NOT NULL, data jsonb );
INSERT INTO employee VALUES (1, 35, '{"name": "Tom Price", "tags": ["Motivated", "SelfLearner"], "onboareded": true}');
Pseudo-Types
PostgreSQL has many special-purpose entries that are called pseudo-types. You can’t use pseudo-types as PostgreSQL column types. There are used to declare or function’s argument or return type.
Each of the available pseudo-types is helpful in situations where a function’s behavior docs do not correspond to simply taking or returning a value of a specific SQL data type.
Name | Description |
---|---|
Any | Function accepts all input data type. |
An array | The function accepts any array data type. |
Any element | The function accepts any data type. |
Any enum | The function accepts any enum data type. |
Nonarray | The function accepts any non-array data type. |
Cstring | Function accepts or returns null-terminated C string. |
Internal | Internal function accepts or returns server-internal data type. |
Language_handler | It is declared to return language handler. |
Record | Find a function which returns an unspecified row type. |
Trigger | A trigger function is used to return trigger. |
It is important that the user who is using this function need to make sure that the function will behave securely when a pseudo-type is used as an argument type.
Best practices using Data types
- Use “text” data type unless you want to limit the input
- Never use “char.”
- Integers use “int.” Use bigint only when you have really big numbers
- Use “numeric” almost always
- Use float in PostgreSQL if you have IEEE 754 data source
Summary
- PostgreSQL offers a rich set of native data types for users
- PostgreSQL supports character data types for storing text values
- PostgreSQL supports two distinct types of numbers: 1. Integers, 2. Floating-point numbers
- A binary string is a sequence of bytes or octets
- PostgreSQL has Network address type to help you optimize storage of network data
- Text search PostgreSQL data structures are designed to support full-text search
- Date/Time PSQL data types are allow date and time information in various formats
- Boolean Postgres field types can hold three values 1. True 2. False 3. Null
- Geometric PostgreSQL data types represent two-dimensional spatial objects
- Enumerated Data types in PostgreSQL is useful for representing rarely changing information such as country code or branch id
- Universally Unique Identifies (UUID) is a 128-bit quantity which is generated by an algorithm
- PostgreSQL has many special-purpose entries that are called pseudo-types
- It is best practice to use “text” data type unless you want to limit the input