Home » PostgreSQL Data Types

PostgreSQL Data Types

by Online Tutorials Library

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

You may also like