PostgreSQL Data TypesOverview of PostgreSQL data types

PostgreSQL supports the following data types:

• Boolean
• Character types such aschar,varchar, andtext.
• Numeric types such as integer and floating-point number.
• Temporal types such as date,time,timestamp, and interval
• UUID for storing Universally Unique Identifiers
• Array for storing array strings, numbers, etc.
• JSON stores JSON data
• hstore stores key-value pair
• Special types such as network address and geometric data.

Boolean

ABoolean data type can hold one of three possible values: true, false, or null. You useboolean orbool keyword to declare a column with the Boolean data type.

When youinsert data into a Boolean column, PostgreSQL converts it to a Boolean value

• 1,yes,y,t,true values are converted totrue
• 0,no,false,f values are converted tofalse.

When youselect data from a Boolean column, PostgreSQL converts the values back e.g., t to true,f tofalse andspace tonull.

Character

PostgreSQL provides threecharacter data types:CHAR(n),VARCHAR(n), andTEXT

• CHAR(n) is the fixed-length character with space padded. If you insert a string that is shorter than the length of the column, PostgreSQL pads spaces. If you insert a string that is longer than the length of the column, PostgreSQL will issue an error.
• VARCHAR(n) is the variable-length character string. TheVARCHAR(n) allows you to store up ton characters. PostgreSQL does not pad spaces when the stored string is shorter than the length of the column.
• TEXT is the variable-length character string. Theoretically, text data is a character string with unlimited length.

Numeric

PostgreSQL provides two distinct types of numbers:

• integers
• floating-point numbers

Integer

There are three kinds of integers in PostgreSQL:

• Small integer (SMALLINT) is a 2-byte signed integer that has a range from -32,768 to 32,767.
• Integer (INT) is a 4-byte integer that has a range from -2,147,483,648 to 2,147,483,647.
• Serial is the same as integer except that PostgreSQL will automatically generate and populate values into theSERIAL column. This is similar toAUTO_INCREMENT column in MySQL orAUTOINCREMENT column in SQLite.

Floating-point number

There are three main types of floating-point numbers:

• float(n)  is a floating-point number whose precision, is at least, n, up to a maximum of 8 bytes.
• realorfloat8is a 4-byte floating-point number.
• numericornumeric(p,s) is a real number with p digits with s number after the decimal point. Thisnumeric(p,s) is the exact number.

Temporal data types

The temporal data types allow you to store date and /or time data. PostgreSQL has five main temporal data types:

• DATE stores the dates only.
• TIME stores the time of day values.
• TIMESTAMP stores both date and time values.
• TIMESTAMPTZ is a timezone-aware timestamp data type. It is the abbreviation fortimestamp with the time zone.
• INTERVAL stores periods.

TheTIMESTAMPTZ is PostgreSQL’s extension to the SQL standard’s temporal data types.

Arrays

In PostgreSQL, you can store anarray of strings, an array of integers, etc., in array columns. The array comes in handy in some situations e.g., storing days of the week, and months of the year.

JSON

PostgreSQL provides two JSON data types:JSON andJSONB for storing JSON data.

TheJSON data type stores plain JSON data that requires reparsing for each processing, whileJSONB data type storesJSON data in a binary format which is faster to process but slower to insert. In addition,JSONB supports indexing, which can be an advantage.

UUID

TheUUID data type allows you to store Universal Unique Identifiers defined by RFC 4122 . TheUUID values guarantee a better uniqueness thanSERIAL and can be used to hide sensitive data exposed to the public such as values ofid in URL.

Special data types

Besides the primitive data types, PostgreSQL also provides several special data types related to geometry and network.

• box – a rectangular box.
• line– a set of points.
• point – a geometric pair of numbers.
• lseg – a line segment.
• polygon – a closed geometric.
• inet – an IP4 address.
• macaddr– a MAC address.

In this tutorial, we have introduced you to the PostgreSQL data types so that you can use them to create tables in the next tutorial.

Last updated onMay 8, 2024

On this page

• PostgreSQL Data TypesOverview of PostgreSQL data types
• Boolean
• Character
• Numeric
• Temporal data types
• Arrays
• JSON
• UUID
• Special data types