PostgreSQL – Data Types

In this article we will look into the various data types available in PostgreSQL. The following data types are supported by PostgreSQL:

  • Boolean
  • Character Types [ such as char, varchar, and text]
  • Numeric Types [ such as integer and floating-point number]
  • Temporal Types [ such as date, time, timestamp, and interval]
  • UUID [ for storing UUID (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]

Now let’s get an overview of the above-mentioned data types.

Boolean –

A Boolean data type can hold one of three values namely, true, false or null. The bool or boolean keyword is used to create a column with Boolean data type. Insertation of data into a Boolean column converts it into the following:

  • 1, yes, y, t, true values are converted to true
  • 0, no, false, f values are converted to false

Selection of data from a Boolean column converts value back as below:



  • t to true
  • f to false
  • space to null

Characters –

PostgreSQL has three character data types namely, CHAR(n), VARCHAR(n) and TEXT.

  • CHAR(n) is the fixed-length character with padded spaces. If you insert or add a string that is shorter than the length of the column, PostgreSQL pads the remaining spaces. If you insert a string that is longer than the length of the column, PostgreSQL raises an error.
  • VARCHAR(n) is the variable-length character string. With VARCHAR(n), you can store up to n characters. No padding of spaces is done 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 has 2 types of numbers namely, integers and floating point numbers.

  • Integer:
    • Small integer ( SMALLINT) is 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 the SERIAL column.
  • Floating-point number:
    • float(n) is a floating-point number whose precision, at least, n, up to a maximum of 8 bytes.
    • realor float8is a 4-byte floating-point number.
    • numeric or numeric(p, s) is a real number with p digits with s number after the decimal point. The numeric(p, s) is the exact number.

Temporal data type –

This data type allows user to store date and time data. PostgreSQL has 5 temporal data type:

  • 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.
  • INTERVAL stores periods of time.

Arrays –

In the PostgreSQL an array column can be used to store an array of strings or an array of integers etc. It can be handy when storing data likes storing days of months, a year, or even a week, etc.

JSON –

PostgreSQL supports 2 type of JSON types namely JSON and JSONB(Binary JSON).The JSON data type stores plain JSON data that requires reparsing for each processing, while JSONB data type stores JSON 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 –

The UUID data type allows you to store Universal Unique Identifiers defined by RFC 4122. The UUID values guarantee a better uniqueness than SERIAL and can be used to hide sensitive data exposed to the public such as values of id in URL.

Special data types –

In addition to the primitive data types, PostgreSQL also supports some special data types that are related to network or geometric. These special data types are listed below:

  • box– a rectangular box.
  • point– a geometric pair of numbers.
  • lseg– a line segment.
  • point– a geometric pair of numbers.
  • polygon– a closed geometric.
  • inet– an IP4 address.
  • macaddr– a MAC address.
My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :

Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.