Javatpoint Logo
Javatpoint Logo

PostgreSQL Data Types

A data type specifies what kind of data you want to store in the table field. While creating a table, for each column, you have to use a datatype. It identifies a particular type of data, like integer, Boolean, floating points, and so on.

In this section, we are going to discuss the various data types used in PostgreSQL.

In PostgreSQL, each database table has many columns and has precise data types for each column. It supports an extensive range of data types.

Also, users can create their custom data type using the CREATE TYPE SQL command. And these data types have the following advantages:

  • Performance:It enhances our performance if we are using these data types correctly and efficiently to store the data values.
  • Validation: The correct use of data types involves the validation of data and dismissal of data outside the scope of the data type.
  • Compactness:It stores efficiently, as a column can store a single type of value.
  • Consistency: The activities in contradiction of columns of the same data type provide reliable results and are usually the fastest.

In PostgreSQL, we have many sets of data types; let us understand them one by one:

PostgreSQL Data Types
  • Numeric datatype
  • Character datatype
  • Date/time datatype
  • Monetary data type
  • Binary data type
  • Boolean data type
  • Enumerated data type
  • Geometric data type
  • Text search data type
  • UUID data type
  • Network address type
  • JSON data type
  • Bit string type
  • XML data type
  • Range data type
  • Arrays
  • Composite data type
  • Object identifiers type
  • Pseudo data type
  • pg-Isn data type

Numeric Data Type

Numeric datatype is used to specify the numeric data into the table. It contains the following:

  • four- and eight-byte floating-point numbers
  • two-, four-, and eight-byte integers
  • Selectable-precision decimals.

The below table contains all numeric data types that support in PostgreSQL:

name description storage size range
smallint stores whole numbers, small range. 2 bytes -32768 to +32767
integer stores whole numbers.use this when you want to store typical integers. 4 bytes -2147483648 to +2147483647
bigint stores whole numbers, large range. 8 bytes -9223372036854775808 to 9223372036854775807
decimal user-specified precision, exact variable up to 131072 digits before the decimal point; up to 16383 digits after the decimal point.
numeric user-specified precision, exact variable
up to 131072 digits before the decimal point; up to 16383 digits after the decimal point.
real
variable-precision, inexact
4 bytes 6 decimal digits precision.
double precision variable-precision, inexact 8 bytes 15 decimal digits precision
serial auto incrementing integer 4 bytes 1 to 2147483647
bigserial large auto incrementing integer 8 bytes 1 to 9223372036854775807

Character Data Type

In PostgreSQL, we have various general purpose character data types, and these data types are used to represent the character type values.

The below table contains all Character data types that are supported in PostgreSQL:

Datatype Explanation
char(size)
Here size is the number of characters to store. Fixed-length strings. Space padded on right to equal size characters.
character(size) Here size is the number of characters to store. Fixed-length strings. Space padded on right to equal size characters.
varchar(size) Here size is the number of characters to store. Variable-length string.
character varying(size) Here size is the number of characters to store. Variable-length string.
text Variable-length string.

Date/Time Data Type

The PostgreSQL supports the complete set of SQL date and times data types. The date/time datatypes are used to represent the columns using date and time values. Based on the Gregorian calendar, the dates are counted.

The below table contains all date/ time data types that are supported in PostgreSQL:

Name Description Storage size Minimum value Maximum value Resolution
timestamp [ (p) ] [ without time zone ] both date and time (no time zone) 8 bytes 4713 bc 294276 ad 1 microsecond / 14 digits
timestamp [ (p) ] with time zone both date and time, with time zone 8 bytes 4713 bc 294276 ad 1 microsecond / 14 digits
date date (no time of day) 4 bytes 4713 bc 5874897 ad 1 day
time [ (p) ] [ without time zone ] time of day (no date) 8 bytes 00:00:00 24:00:00 1 microsecond / 14 digits
time [ (p) ] with time zone times of day only, with time zone 12 bytes 00:00:00+1459 24:00:00-1459 1 microsecond / 14 digits
interval [ fields ] [ (p) ] time interval 12 bytes -178000000 years 178000000 years 1 microsecond / 14 digits

Monetary type:

Name Description Storage size Range
money currency amount 8 bytes -92233720368547758.08 to +92233720368547758.07

Binary Data Types

The bytea data type allows storage of binary strings as we can see in the below table:

Name Storage size Description
bytea 1 or 4 bytes plus the actual binary string variable-length binary string

Boolean type

PostgreSQL contains the standard SQL type boolean; The Boolean type has many states such as true, false, and a third state, unknown, represented by the SQL null value.

Name Description Storage size
boolean it specifies the state of true or false. 1 byte

The Boolean datatype input function receives these string demonstrations for the true and false state:

PostgreSQL Data Types

Enumerated Types

In PostgreSQL, the Enumerated data types include a static, ordered set of values.

It is similar to enum types which are compatible with a various programming language. The Enumerated data type is represented in a table with foreign keys to ensure data integrity.

For Example:

Geometric Data Type

Geometric data types represent two-dimensional spatial objects. The most fundamental type, the point, forms the basis for all of the other types.

Name Storage size Representation Description
point 16 bytes point on a plane (x,y)
line 32 bytes infinite line (not fully implemented) ((x1,y1),(x2,y2))
lseg 32 bytes finite line segment ((x1,y1),(x2,y2))
box 32 bytes rectangular box ((x1,y1),(x2,y2))
path 16+16n bytes closed path (similar to polygon) ((x1,y1),...)
path 16+16n bytes open path [(x1,y1),...]
polygon 40+16n polygon (similar to closed path) ((x1,y1),...)
circle 24 bytes circle <(x,y),r> (center point and radius)

Text Search data type

In PostgreSQL, the full-text search data type is used to search over a collection of natural language documents. We have two categories of data types that are compatible with full-text search.

Data types Description
tsvector It is used to display a document in a form, which enhance text search.
tsquery It is used to represent a text query.

UUID data types

The UUID stands for Universally Unique identifiers is a 128-bit quantity which is created by an algorithm. It is the best-suited data type for the primary keys. The UUID is written as a group of lower-case hexadecimal digits through multiple sets separated by hyphens.

For example:

Note: PostgreSQL also agrees to take alternative forms of UUID inputs such as no hyphens, all capital case, braces, and so on.

Network Address Data type

PostgreSQL provides data types to store Mac Address, IPv4, and IPv6, as we can see in the below table. It enhances these types other than plain text types to contain network address as these offer input error checking and particular function and operators.

The below table contains all network address data types that are supported in PostgreSQL:

Data type Description Storage Size
inet It stores the IPv4 and IPv6 hosts and networks. 7 or 19 bytes
cidr It is used to store the IPv4 and IPv6 networks. 7 or 19 bytes
macaddr It stores the MAC addresses. 6 bytes

JSON Data type

PostgreSQL provides two kinds of data types for storing the JSON (JavaScript Object Notation) data.

  • JSON
  • JSONB

Json

It is an extension of a text data type with JSON validation. In this, we can insert the data quickly, but the data retrieval is comparatively slow. It saves inputted data just the way it contains the whitespace. It is also reprocessing on the data retrieval.

Jsonb

It is a binary representation of the JSON data. It is also compatible with indexing and also improves the whitespace to make the retrieval quicker. In this, the insertion is slow, but the data retrieval is more rapid, and reprocessing is required on the data retrieval.

Bit string types

The bit strings data type contains two categories of strings that are 1's and 0's. The bitmasks can be stored with the help of these strings. In this, we have two kinds of SQL bit, such as:

  • bit varying(n)
  • bit (n)

Here, n is a positive integer.

XML type

In PostgreSQL, the XML data type is used to store the XML data. The function of the XML data type is to check whether that the input XML is well-formed, and also there are support functions to perform type-safe operations on it.

For Example:

Range Types

These data types are used to display a range of values of some element types, known as the range's subtype. It also signifies several elements of values in a single range value. In this, we can also create our range types.

In PostgreSQL, we have following built-in range types:

Built-in range types Description
tsrange Range of timestamp without time zone
tstzrange Range of timestamp with time zone
daterange Range of date
int4range Range of integer
int8range Range of bigint
numrange Range of numeric

Array Type

In this, the PostgreSQL provides a column of tables as a variable-length and the multidimensional array. We can create any user-defined base type, built-in, composite, and enumerated type arrays.

Here, we can perform various operations on arrays such as declare, insert, accessing, modifying, and searching.

Composite Types

In PostgreSQL, the composite data type is used to signify the structure of a row or record as a list of file names and data types.

Pseudo data type

In PostgreSQL, the data types are pseudo types, which are used to contain many special-purpose entries. And it is used to declare a result type or the function's argument, but it is not compatible to use as a column data type.

The below table contains some of the commonly used pseudo data types that support in PostgreSQL:

Pseudo data types Description
any It shows that a function accepts any input data type.
anyelement It accepts any data type.
anyarray It shows a function that accepts any array data type
anyenum It accepts any enum data type
anyrange It accepts any range of data type
cstring It is used to specify that a function accepts or returns a null-terminated C string.
language_handler A procedural language call handler is declared to return language_handler.
fdw_handler The fdw (foreign-data wrapper) handler is declared to return fdw_handler.
record It is used to specify a function which is taking or returning an unspecified row type.
trigger It is declared to return the trigger.
pg_ddl_command It is used to represent the DDL commands that are available to event triggers.
void It is used to specify that a function returns no value.

Object identifier (OIDs) type

These types of data types are used as primary keys for several system tables. The oid type represents an object identifier and currently implemented as an unsigned four-byte integer. In huge databases or even in large individual tables, it is not big enough to offer database-wide individuality.

Object identifiers are used for references to system tables. Beyond comparison, the oid type itself has few operations that can be cast to integer, and can be manipulated using the standard integer operators.

The below table contains all the object identifier data types that are supported in PostgreSQL:

Name Description References Value Example
oid numeric object identifier Any 564182
regproc function name pg_proc Sum
regprocedure function with argument types pg_proc sum(int4)
regoper operator name pg_operator +
regoperator operator with argument types pg_operator *(integer,integer) or -(NONE,integer)
regclass relation name pg_class pg_type
regtype data type name pg_type Integer
regnamespace namespace name pg_namespace pg_catalog
regconfig text search configuration pg_ts_config English
regdictionary text search dictionary pg_ts_dict Simple

pg_lsn Type

The pg_lsn data type can be used to store Log Sequence Number (LSN) data, a pointer to a location in the XLOG. It is used to signify the XLogRecPtr and an internal system type of PostgreSQL. The pg_lsn type is compatible with the standard comparison operators, such as > and =.

Note:

While using the data types, we can refer to the following points:

  • If we have an IEEE 754 data source, we can use the float data type
  • For integer data type, we can use int.
  • Never use char.
  • If we want to limit the input, we can apply a text data type.
  • When we have vast numbers, we can use bigint only.

Overview

  • The character data types are used to store text values.
  • The Network address type is used to optimize the storage of network data.
  • A binary string is a classification of bytes or octets.
  • PostgreSQL provides two different types of numbers, such as Floating-point numbers and integers.
  • The range data types are used to display a range of values of some element types.
  • The Boolean data type has three values, such as True, False, and Null.
  • The UUID data type is a 128-bit quantity, which is made by an algorithm.
  • The composite data type is used to signify the structure of a row or record.
  • PostgreSQL, the enumerated Data types, are rarely used to demonstrate the modified information like branch id or country code.
  • To define date and time information in several formats, PostgreSQL provides Date and Time data type.
  • The object identifiers data type represents an object identifier.
  • PostgreSQL designed the text search data to support full-text search.
  • The Geometric data type is used to signify two-dimensional spatial objects.
  • In PostgreSQL, the pseudo type is used for many special-purpose entries.
  • The pg_lsn data type is used to store Log Sequence Number (LSN) data.

Next TopicInstall PostgreSQL




Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA