PostgreSQL Data Types + Examples

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 −

1. Consistency: Operations against columns of the constant data types give reliable outcomes and are usually the fastest.
2. 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.
3. Validation: Correct use of data types implies format validation of data and rejection of data outside the scope of data type.
4. 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:

Read PostgreSQL Loop Examples

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:

1. The id column is the primary key column that identifies the order.
2. 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