In this PostgreSQL tutorial, we are going to learn about PostgreSQL Data Types. Here we will learn about different data types available in PostgreSQL, and we will also cover the following list of topics.
- Postgresql data types
- Postgresql data types and sizes
- Postgresql data types numeric
- Postgresql data types with example
- Postgresql data types for double
- Postgresql data types datetime
- Postgresql data types text vs varchar
PostgreSQL Data Types with Examples
PostgreSQL upholds a vast range of Data Types. It contains several data types that include Boolean, Numeric, Character, Temporal, Array, JSON, UUID, and special data types.
While generating a table, for every column, we explain a data type expressly whatever type of data we want to store in the table fields.
This empowers a few advantages −
- Consistency: Operations against columns of the constant data types give reliable outcomes and are usually the fastest.
- Performance: Correct utilization of data types offers the foremost productive reposting of knowledge. The values hold on are often handled speedily, which upgrades the exhibition.
- Validation: Correct use of data types implies format validation of data and rejection of data outside the scope of data type.
- Compactness: As a column will store one type of value, it is kept in a compressed method.
Review of PostgreSQL data types. PostgreSQL has the following data types:
- Numeric data types like integer and floating-point numbers.
- Temporal types like date, time, timestamp, and interval
- UUID for storing globally distinctive Identifiers
- ARRAY for storing array strings, numbers, etc.
- JSON will store JSON data
- Special types like network address and geometric data.
- Boolean types like true, false, or null.
- Character types therefore as text, char, and varchar
Read Drop Database PSQL
Numeric data types in PostgreSQL
PostgreSQL allocates two distinct kinds of numbers:
- integers
- floating-point numbers
Temporal data types in PostgreSQL
The temporal data types permit us to keep date and time data. PostgreSQL has five foremost temporal data types:
- The DATE will store the dates only.
- The TIME will store the time of day values.
- TIMESTAMP stores both date and time values.
- TIMESTAMPTZ is a timezone-aware timestamp data type which is the abbreviation for timestamp with the time zone.
- The INTERVAL will store the periods of time.
The TIMESTAMPTZ is PostgreSQL’s expansion to the SQL standard’s temporal data types. Here’s an example of a DATE
data type that uses the CURRENT_DATE()
function:
CREATE TABLE Student(
id INT NOT NULL,
name TEXT,
join_date DATE DEFAULT CURRENT_DATE
);
This statement will return results that look like the following:
SELECT * FROM Student;
Read PostgreSQL group by
UUID data type in PostgreSQL
A UUID which is also named as Universally Unique Identifiers is drafted as an order of lower-case hexadecimal digits, in various classifications distinguished by hyphens, particularly a grouping of eight digits, after that three groups of four digits, afterward a group of 12 digits, for an entire total of 32 digits producing the 128 bits.
For Example: 660e8500-e25b-40d4-a496-336633990000.
ARRAY in PostgreSQL
PostgreSQL permits a column of tables as a variable-length and multidimensional array. We will build any user-defined base type, built-in, composite, and enumerated type arrays.
We will implement many operations on arrays as well as declare, insert, access, modify, and search in PostgreSQL.
PostgreSQL reserves an array of strings, an array of integers, etc., in array columns. An array that seems in sure circumstances like storing days of the week, months of the year.
In the below example, we will create a table named Students with the contact column defined as a text array:
CREATE TABLE Students (
id int PRIMARY KEY,
name VARCHAR (100),
contact TEXT []
);
The command will run successfully:

Now, we will insert the array values:
INSERT INTO Students
VALUES
(
1,
'Richard Jener',
ARRAY [ '(408)-643-9245',
'(408)-667-8920' ]
);
The insertion will run successfully:

JSON in PostgreSQL
PostgreSQL supports two JSON data types: JSON and JSONB for putting away JSON data.
The JSON data type stores plain JSON data which needs reparsing for every processing although JSONB data type stores JSON data in a binary format and that’s quicker to measure however more slow to insert.
Also, JSONB supports indexing, which can be a benefit. Let’s understand with the help of an example by creating a table:
CREATE TABLE goods (
id serial NOT NULL PRIMARY KEY,
info json NOT NULL
);
The products the table comprises of two columns:
- The
id
column is the primary key column that identifies the order. - The
info
column stores the data in the form of JSON.
Insert JSON data
Let’s insert the data into a JSON column, we will ensure that data is in a valid JSON format. The below statement inserts a new row into the table.
INSERT INTO goods (info)
VALUES('{ "customer": "David Geller", "items": {"product": "Vodka","qty": 4}}');
It means "David Geller"
bought 4 bottles of vodka. The below statement inserts multiple rows at the same time.
INSERT INTO goods (info)
VALUES('{ "customer": "Christina Applegate", "items": {"product": "Diaper","qty": 24}}'),
('{ "customer": "Chandler Bing", "items": {"product": "Toy Car","qty": 1}}'),
('{ "customer": "Phoebe Buffay", "items": {"product": "Toy Train","qty": 2}}');
We will use a SELECT statement to query JSON data, which is similar to querying other native data types which will generate the following output:
SELECT info FROM goods;

Special Data Types in PostgreSQL
Apart from primary data types, PostgreSQL also gives various data types linked to geometric and network.
- box: the representation of a box is a rectangular box. The storage size of box is 32 bytes.
- macaddr: The representation of macaddr is a MAC address whose storage size is 6 bytes.
- line: the representation of a line is an Infinite line that is not fully implemented. The storage size of a line is 32 bytes.
- polygon: the representation of a polygon is a polygon that is similar to closed path. The storage size of a polygon is 40+ 16n.
- point: the representation of point is a point on a plane. The storage size of a point is 16 bytes.
- lseg: the representation of a lseg is a line segment. The storage size of lseg is 32 bytes.
- inet: an IP4 and IPV6 address.
Read PostgreSQL Date Difference
Boolean Data Type in PostgreSQL
PostgreSQL gives the standard SQL type Boolean. A Boolean data type carries one of three potential values: true, false, and a third state, null, which is illustrated by the SQL null value.
A boolean or bool is a keyword that declares a column with the Boolean data type.
When we insert data into a Boolean column, PostgreSQL transforms it into a Boolean value
- 1, yes, y, t, true values turn to true
- 0, no, false, f values turns to false.
When we select data from a Boolean column, PostgreSQL transforms the values back e.g., t to true, f to false, and space to null.
The storage size of a boolean is 1 byte. Let’s understand with the help of an example. First, create a table item_availability
to log which products are available:
CREATE TABLE item_availability (
item_id INT PRIMARY KEY,
available BOOLEAN NOT NULL
);
Now insert some data into the item_avaliability
table. We will use various literal value for the boolean values.
INSERT INTO item_availability (item_id, available)
VALUES
(100, TRUE),
(200, FALSE),
(300, 't'),
(400, '1'),
(500, 'y'),
(600, 'yes'),
(700, 'no'),
(800, '0');
Now, use the following statement to check for the item’s availability:
SELECT *
FROM item_availability
WHERE available = 'yes';

Character Data types in PostgreSQL
PostgreSQL has three-character data types: CHAR(n), VARCHAR(n), and TEXT
- CHAR(n) is a certain sized character with space padded. If we insert a string that is smaller than the length of the column, PostgreSQL pads spaces. If we insert a string that is longer than the length of the column, PostgreSQL will matter a glitch.
- VARCHAR(n) is the variable-length character string. With VARCHAR(n), we store up to n characters. PostgreSQL doesn’t pad spaces when the stored string is smaller than the length of the column.
- TEXT is the variable-length character string. Hypothetically text data is a character string with unlimited length.
Read: PostgreSQL WHERE
PostgreSQL Numeric
In this tutorial, we are going to learn about the PostgreSQL NUMERIC type which stores numeric data.
The NUMERIC type stores numbers with a lot of digits. Generally, we use the NUMERIC type for numbers that insist on correctness includes financial amounts or quantities.
Following represents the syntax of the NUMERIC type:
NUMERIC(precision, scale)
The accuracy within the above than syntax is the total number of digits and also the scale is that number of digits within the fraction part. For example, the number 1234.567 has a precision of 7 and a scale of 3.
The NUMERIC type will hold a value up to 131,072 digits before the decimal point 16,383 digits afterward the decimal point.
The scale of the NUMERIC type is zero or positive. The related shows the syntax of NUMERIC type with scale zero:
NUMERIC(precision)
If we exclude both precision and scale, we can store any precision and scale up to the limit of the precision and scale mentioned above.
NUMERIC
In PostgreSQL, the NUMERIC and DECIMAL types are identical along two of them are likewise a piece of the SQL standard.
If in the case of accuracy isn’t necessary, we ought not to use the NUMERIC type since estimations on NUMERIC values are generally slower than integers, floats, and double precisions.
Example: Firstly we will create a new table named products for the demonstration
DROP TABLE IF EXISTS items;
CREATE TABLE items (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
cost NUMERIC(6,3)
);
Secondly, we will insert some products with cost whose scales exceed the scale declared in the price column:
INSERT INTO items (name, cost)
VALUES ('Speakers',400.214),
('Earphones',700.21);
Because the scale of the cost column is 2, PostgreSQL rounds the value 400.150 up to 400.22 and rounds the value 400.214 down to 400.21
The following query returns all rows of the products table:
SELECT * FROM items;

If we store a value whose accuracy overtakes the declared precision, PostgreSQL raises an error which is shown in the example:
INSERT INTO items (name, price)
VALUES('Speakers',123456.21);
PostgreSQL will generate the following error:

Read: PostgreSQL CASE
Postgresql datatype text vs varchar
In this tutorial, we will understand the PostgreSQL character data types as well as CHAR, VARCHAR, and TEXT. Now, we will see the differences between TEXT and VARCHAR.
Both TEXT and VARCHAR have a higher limit at 1 Gb, and there is no performance difference between them.
The main variation between TEXT and VARCHAR(n) is that we can limit the maximum length of a VARCHAR column such as VARCHAR(255) does not admit inserting a string more than 255 characters long.
In a worth embedded to a TEXT column has trailing spaces, PostgreSQL doesn’t shorten them, and they are expository in analysis:
CREATE TABLE globe (a1 TEXT);
INSERT INTO globe VALUES ('A ')
SELECT '''' || a1 || '''' FROM globe;
SELECT COUNT(a1) FROM globe WHERE a1 = 'A'
SELECT COUNT(a1) FROM globe WHERE a1 = 'A ';
Here is the output of the query.

- PostgreSQL supports CHAR, VARCHAR etc data types. The VARCHAR and TEXT are varying length character types.
- We can use VARCHAR(n) if we want to approve the length of the string (n) before inserting it into or updating to a column.
- VARCHAR when without the length specifier and TEXT are equivalent.
Read: PostgreSQL WHERE IN
PostgreSQL Datatypes sizes
Geometric Type: These data types will illustrate two-dimensional spatial objects. The basic type, the point, forms the basis for all of another type:
NAME | REPRESENTATION | STORAGE SIZE | DESCRIPTION |
box | Rectangular box | 32 bytes | ((x1,y1),(x2,y2)) |
line | Infinite line(not fully implemented) | 32 bytes | ((x1,y1),(x2,y2)) |
point | the point on a plane | 16 bytes | (x,y) |
circle | Circle | 24 bytes | <(x,y),r> (center point and radius) |
lseg | Finite line segment | 32 bytes | ((x1,y1),(x2,y2)) |
path | Closed path(similar to polygon) | 16+16n bytes | ((x1,y1),…) |
path | Open path | 16+16n bytes | [(x1,y1)…] |
polygon | Polygon(similar to closed path) | 40+16n | ((x1,y1),…) |
Network Address Type: PostgreSQL offers data types to store MAC addresses, IPv4 and IPv6. It is higher to use these types rather than plain text types to store network addresses as a result these supply error checking and specialized operators and functions.
Name | Description | Storage size |
inet | IPv4 and IPv6 hosts and networks | 7 or 19 bytes |
cidr | IPv4 and IPv6 networks | 7 or 19 bytes |
macaddr | MAC addresses | 6 bytes |
Boolean type: Boolean data type is given by PostgreSQL. The Boolean data type can have the states true, false, and a third state, unknown, which is addressed by the SQL null value.
Name | Description | Storage Size |
boolean | state true or false | 1 byte |
Date/Time types: PostgreSQL upholds a full set of SQL date and time types, as displayed in the table below. According to the Gregorian calendar dates are counted. Now, every type has a resolution of 1 microsecond / 14 digits except the date type, whose goal is the day.
Name | Description | High Value | Low value | Storage size |
TIMESTAMPTZ | both date and time, with time zone | 294276 AD | 4713 BC | 8 bytes |
timestamp [(p)] [without time zone ] | both date and time(no time zone) | 294276 AD | 4713 BC | 8 bytes |
interval [fields ] [(p) ] | time interval | 178000000 years | – 178000000 years | 12 bytes |
time [ (p)] [ without time zone ] | time of day (no date) | 24:00:00 | 00:00:00 | 8 bytes |
time [ (p)] with time zone | times of day only, with time zone | 24:00:00-1459 | 00:00:00+1459 | 12 bytes |
date | date(no time of day) | 5874897 AD | 4713 BC | 4 bytes |
Character Types: The below table lists the purpose of character types accessible in PostgreSQL.
Name | Description |
text | variable-length with limit |
character varying(n), varchar(n) | variable- length with a limit |
character(n), char(n) | fixed-length, blank padded |
Numeric Type: It consists of two-byte, four-byte, and eight-byte integers, four-byte and eight-byte floating-point numbers, and selectable-precision decimals. Here is the list of obtainable data types.
Name | Description | Range | Storage size |
integer | a typical choice for integer | -2147483648 to +2147483647 | 4 bytes |
decimal | user-specified precision, exact | up to 131072 digits before the decimal point; up to 16383 digits after the decimal point | variable |
numeric | user-specified precision, exact | up to 131072 digits before the decimal point; up to 16383 digits after the decimal point | variable |
real | variable-precision,inexact | 6 decimal digits precision | 6 bytes |
serial | autoincrementing integer | 1 to 2147483647 | 4 bytes |
bigserial | large autoincrementing integer | 1 to 9223372036854775807 | 8 bytes |
smallserial | small autoincrementing integer | 1 to 32767 | 2 bytes |
smallint | small-range integer | -32768 to +32767 | 2 bytes |
bigint | large-range integer | -9223372036854775808 to 9223372036854775807 | 8 bytes |
double precision | variable-precision,inexact | 15 decimal digits precision | 8 bytes |
Read: PostgreSQL TO_NUMBER() function
Postgresql data types for double
In PostgreSQL, a numeric data type is a DOUBLE PRECISION which is also called alternate name float8.
Double-precision values are managed as floating-point values that means that any rounding will happen if we try to store a value with “too many” decimal digits like if we try to store the result of 2/3, there would be some rounding when the 15th digit was attained.
Sometimes there is no actual result of this estimation, but there are circumstances where absolute precision is essential with the NUMERIC data type is the greater option.
Data Type | Range | Description |
double-precision, float, float8 | 15 decimal digits precision | decimal, floating-point numeric data, integer values |
Create a database and table in PostgreSQL: Now, we will create a table with a DOUBLE PRECISION data type. Firstly, we have to create a database in PostgreSQL in which we will use the command shown below:
CREATE DATABASE any_db;
Now we will create a table:
CREATE TABLE TABLE_NAME(
COLUMN1 + DATA_TYPE + CONSTRAINT [OPTIONAL],
COLUMN2 + DATA_TYPE + CONSTRAINT [OPTIONAL]);
Now, we will see the statement used to create our table:
CREATE TABLE abc(
id SERIAL PRIMARY KEY,
str_col VARCHAR(100),
int_col INT NOT NULL,
boolean_col BOOL,
float_col DOUBLE PRECISION
);
Use the command \d abc; To view the table information, the output of this command will look like this:

Insert some values inside the DOUBLE PRECISION column.
For inserting our data, we can use an INSERT statement in our DOUBLE PRECISION column and another column of the table:
INSERT INTO abc (str_col, int_col, boolean_col, float_col)
VALUES('Glass', 7896, TRUE, 0879654321),
('Images', 7768, FALSE, 09876544568),
('Tutor', 6754, TRUE, 09417689765);
To see the values in the float_column
, use the simple SELECT
statement shown below:
SELECT id, float_col FROM demo;
Now, here is the following output:

Read: PostgreSQL TO_TIMESTAMP function
PostgreSQL Data Type Datetime
PostgreSQL has a wide range of date/time data types which is already shown above in TABLE 1.4. Now we will discuss the date/time inputs. Table 1.8 shows some possible inputs for the datetype.
Example | Description |
January 8, 1999 | unambiguous in any datestyle input mode |
1999-Jan-08 | January 8 in any mode |
99-Jan-08 | January 8 in YMD mode, else error |
1/8/1999 | January 8 in MDY mode; August 1 in DMY mode |
Time Input: These types comprise a time of day followed by an optional time zone of valid input. If a time zone is described in the input for time without time zone which is mutely unnoticed.
We can also define a date but it is, however unnoticed, excluding when you use a time zone name that involves a daylight-savings rule, including America(New York).
In this case, specifying the date is required to determine whether standard or daylight-savings time applies in which the proper time zone offset is recorded in the time with the time zone value.
Example | Description |
04:05:06 | ISO 8601 |
04:05 AM | same as 04:05; AM does not affect the value |
04:05 PM | same as 16:05; input hour must be <= 12 |
04:05:06 PST | time zone specified by the abbreviation |
Date/Time Output: In PostgreSQL, the ISO format is by default. The output format of the DateTime data type is commonly set to a minimum of one of the four styles ISO 8601, the German, SQL, traditional POSTGRES, which uses the command SET datestyle.
The SQL standard needs the usage of the ISO 8601 format. Below Table 1.10 will show examples of each output style. The output of the DateTime data type is absolutely the date or time part in accordance with the given examples.
Example | Style Specification | Description |
17.12.1997 07:37:16.00 PST | German | regional style |
12/17/1997 07:37:16.00 PST | SQL | traditional style |
1997-12-17 07:37:16-08 | ISO | ISO 8601/SQL standard |
Wed Dec 17 07:37:16 1997 PST | POSTGRES | original style |
You may also like to read the following PostgreSQL articles.
- Postgresql change column data type
- PostgreSQL TO_CHAR function
- How to backup PostgreSQL database
- PostgreSQL Export Table to CSV
- PostgreSQL drop all tables
- PostgreSQL list databases
In this PostgreSQL tutorial, we have learned about PostgreSQL Data Types. Here we have covered different data types available in PostgreSQL, and we have also covered the following list of topics.
- Postgresql data types
- Postgresql data types and sizes
- Postgresql data types numeric
- Postgresql data types with example
- Postgresql data types for double
- Postgresql data types datetime
- Postgresql data types text vs varchar
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.