PostgreSQL Tutorial

  • Home
  • Administration
  • Views
  • Triggers
  • Stored Procedures
  • Interfaces
    • PostgreSQL PHP
    • PostgreSQL Python
    • PostgreSQL JDBC
Home / PostgreSQL Tutorial / PostgreSQL Data Types

PostgreSQL Data Types

Summary: in this tutorial, you will learn about PostgreSQL data types including Boolean, character, number, temporal, special types and array.

PostgreSQL supports the following data types:

  • Boolean
  • Character
  • Number
  • Temporal i.e., date and time-related data types
  • Special types
  • Array

Boolean data type

A Boolean data type can hold one of two possible values: true or false. In case the value is unknown, the NULL value is used.

You use boolean or bool keyword when you declare a column that has Boolean data type.

When you insert data into a Boolean column, PostgreSQL will convert it into the Boolean value e.g., 1, yes, y, t, true are converted to true, and 0, no, n false, f are converted to false.

When you select data from a Boolean column, PostgreSQL display t for true, f for false and space character for NULL.

Character data types

PostgreSQL provides three character data types: char(n), varchar(n), and text

  •  char(n) is the fixed-length character with blank padded. If you insert a string that is shorter than the length of the column, PostgreSQL will pad 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. You can store up to n characters with variable-length character strings. PostgreSQL does not pad spaces when the stored string is shorter than the length of the column.
  •  text is variable-length character strings that you do not need to specify the length in the type declaration. Theoretically, a text is an unlimited length character string.

Number data types

PostgreSQL provides two distinct types of numbers:

  • integers
  • floating-point numbers

Integer

There are three kinds of integers:

  • Small integer ( smallint)  is 2-byte signed integer that has a range of (-32768,32767)
  • Integer ( int) is 4-byte integer that has a range of (-214783648, -214783647)
  • Serial is the same as integer except that PostgreSQL populate value into the column automatically. This is similar to AUTO_INCREMENT attribute in other database management systems.

Floating-point number

There three main types of floating-point numbers:

  • float(n)  is a floating-point number whose precision, at least, n, up to a maximum of 8 bytes.
  • realor float8is a double-precision (8-byte) floating-point number.
  • numericor numeric(p,s) is a real number with p digits with s number after the decimal point. The numeric(p,) is the exact number.

Temporal data types

The temporal data types store date and time-related data. There are five main temporal data types in PostgreSQL:

  • date stores date data
  • time stores time data
  • timestamp stores data and time
  • interval stores the difference in timestamps
  • timestamptz store both timestamp and timezone data. The timestamptz is a PostgreSQL’s extension to the temporal data type.

Special data types

Besides the primitive data types, PostgreSQL also provides several special data types related to geometric 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.

Arrays

In PostgreSQL, you can store an array of strings, an array of integers, etc., in a column of a table by using an array. The array comes to handy in some situations e.g., storing days of the week, months of the year, etc.

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.

Related Tutorials

  • Understanding PostgreSQL Timestamp Data Types
  • PostgreSQL Boolean Data Type with Practical Examples
  • Using PostgreSQL SERIAL To Create Auto-increment Column
  • PostgreSQL Character Types: CHAR, VARCHAR, and TEXT
Previous Tutorial: PostgreSQL DELETE
Next Tutorial: PostgreSQL CREATE TABLE

PostgreSQL Quick Start

  • What is PostgreSQL?
  • Install PostgreSQL
  • Connect to Database
  • Download PostgreSQL Sample Database
  • Load Sample Database
  • Explore Server and Database Objects

PostgreSQL Fundamentals

  • PostgreSQL Select
  • PostgreSQL Order By
  • PostgreSQL Select Distinct
  • PostgreSQL Where
  • PostgreSQL LIMIT
  • PostgreSQL IN
  • PostgreSQL Between
  • PostgreSQL Like
  • PostgreSQL Inner Join
  • PostgreSQL Left Join
  • PostgreSQL Full Outer Join
  • PostgreSQL Cross Join
  • PostgreSQL Natural Join
  • PostgreSQL Group By
  • PostgreSQL Having
  • PostgreSQL Union
  • PostgreSQL Intersect
  • PostgreSQL Except
  • PostgreSQL Subquery
  • PostgreSQL Insert
  • PostgreSQL Update
  • PostgreSQL Delete
  • PostgreSQL Data Types
  • PostgreSQL Create Table
  • PostgreSQL Alter Table
  • PostgreSQL Drop Table
  • PostgreSQL Truncate Table
  • PostgreSQL CHECK Constraint
  • PostgreSQL Not-Null Constraint
  • PostgreSQL Foreign Key
  • PostgreSQL Primary Key
  • PostgreSQL UNIQUE Constraint

About PostgreSQL Tutorial

PostgreSQLTutorial.com is a website dedicated to developers and database administrators who are working on PostgreSQL database management system.

We constantly publish useful PostgreSQL tutorials to keep you up-to-date with the latest PostgreSQL features and technologies. All PostgreSQL tutorials are simple, easy-to-follow and practical.

Recent PostgreSQL Tutorials

  • PostgreSQL Recursive View
  • Learn PostgreSQL Recursive Query By Example
  • Creating Updatable Views Using the WITH CHECK OPTION Clause
  • PostgreSQL Upsert Using INSERT ON CONFLICT statement
  • How to Generate a Random Number in A Range
  • Using PostgreSQL ADD COLUMN to Add One or More Columns To a Table
  • PostgreSQL Character Types: CHAR, VARCHAR, and TEXT
  • Using PostgreSQL SERIAL To Create Auto-increment Column
  • PostgreSQL Boolean Data Type with Practical Examples
  • Understanding PostgreSQL Timestamp Data Types

More Tutorials

  • PostgreSQL PHP
  • PostgreSQL Python
  • PostgreSQL JDBC
  • PostgreSQL Functions
  • PostgreSQL Resources

Site Info

  • Home
  • About Us
  • Contact Us
  • Privacy Policy

Copyright © 2016 by PostgreSQL Tutorial Website. All Rights Reserved.