PostgreSQL Data Types

Data Types in PostgreSQL

PostgreSQL is a powerful open-source relational database management system (RDBMS) that supports a wide range of data types. Data types in PostgreSQL define the kind of data that can be stored in a column of a table. They determine the range of values that can be assigned to a variable, the operations that can be performed on the data, and the storage requirements for the data.

PostgreSQL provides a rich set of built-in data types. Additionally, PostgreSQL allows users to define their custom data types, providing flexibility and extensibility.

Data Types in PostgreSQL:

1. Numeric Types:

   • Integer: Stores whole numbers without decimal places.
   • Decimal: Stores numbers with a fixed number of decimal places.
   • Float: Stores floating-point numbers with variable precision.

2. Character Types:

   • Character: Stores fixed-length strings.
   • Varchar: Stores variable-length strings.
   • Text: Stores unlimited-length strings.

3. Date/Time Types:

   • Date: Stores dates without time.
   • Time: Stores time of day without a date.
   • Timestamp: Stores both date and time.
   • timestamptz: Stores both date and time with timezone.
   • Interval: Stores a period or duration.

4. Boolean Type:

   • Boolean: Stores true or false values.

5. Network Address Types:

   • Inet: Stores IPv4 and IPv6 network addresses.
   • Cidr: Stores IPv4 and IPv6 network addresses with their associated subnet masks.
   • Macaddr: Stores the MAC addresses.

6. Geometric Types:

   • Point: Stores a 2D point in a Cartesian coordinate system.
   • Line: Stores an infinite line in a Cartesian coordinate system.
   • Polygon: Stores a closed geometric shape with straight sides.

7. Array Types:

   • Array: Stores a fixed-length or variable-length array of any data type.

8. JSON Types:

   • JSON: Stores JSON (JavaScript Object Notation) data.
   • JSONB: Stores JSON data in a binary format for efficient storage and retrieval.

9. Other Types:

   • UUID: Stores universally unique identifiers.
   • Enum: Stores a predefined list of values.

These are just some of the data types available in PostgreSQL. Every data type possesses unique characteristics and is suitable for specific usage scenarios. This flexibility allows developers to choose the most appropriate type for their specific data storage requirements.

Boolean

The Boolean data type in PostgreSQL represents a logical value that can be either true or false. The keyword representation for the Boolean data type is boolean. It uses one byte of storage. In Postgres data types, the Boolean type is used to filter and sort data based on specific conditions. For example, you can use the Boolean data type to filter out all the active users in a user table by setting the condition to true.

Example:

In this example, we are creating a table named users in a PostgreSQL database. The table has three columns: id, name, and active. The active column is of type BOOLEAN and has a default value of true.

Output:

Character Types

PostgreSQL provides a wide range of character data types to store textual information.

• The varchar(n) (character varying) data type allows variable-length strings. It does not add padding when the length of data is smaller than length n.
• The char(n) (character) data type stores fixed-length strings. When data is smaller than length n, padding is added to the data.
• The text data type is used for storing large amounts of textual data.

For example, you are storing the names of customers in a database table. Then, you can use the character varying data type i.e., varchar to accommodate varying lengths of names.

Example:

In this example, we are creating a table called character_types in a PostgreSQL database. The table consists of four columns: id, name, email, and address. The name and email columns are of type CHAR(50) and VARCHAR(100) respectively. The address column is of type TEXT and can store larger amounts of text.

Output:

Numeric Types

PostgreSQL offers several numeric data types to store numeric values with different precision and scale requirements. These include the following:-

• The smallint, integer, and bigint data types are used for storing whole numbers of different sizes.
• The decimal data type allows for accurate calculations and comparisons of decimal values.
• The real and double precision data types are used for storing floating-point values. But they may not provide the same level of precision as the decimal data type.

For example, if you are storing currency values in a table, using the decimal data type with appropriate precision and scale will ensure accurate calculations and comparisons.

Example:

In this example, we are creating a table named numeric_types. The table consists of eight columns: id, smallint_col, integer_col, bigint_col, decimal_col, numeric_col, real_col, and double_col.

Output:

Temporal Types

PostgreSQL provides various temporal data types to handle date and time-related information. These include the following:-

• The date data type is used for storing dates in PostgreSQL.
• The time data type represents time values.
• The timestamp data type combines both date and time information.
• The timestamptz data type stores a date and time value with timezone information.
• The interval data type is used to store a duration or period.

For example, if you need to store and compare dates, the date data type is suitable. On the other hand, if you require precise timestamps, the timestamp data type should be used.

Example:

In this example, we are creating a table named temporal_types. This table consists of six columns with different temporal types: id, date_col, time_col, timestamp_col, timestamptz_col, and interval_col.

Output:

Special Types

PostgreSQL offers special types to handle specific requirements beyond the standard data types.

• Geometric types are available for storing spatial data of shapes, points, and lines.
• Network address types are designed for storing IP addresses.
• Bit strings are used for storing binary data.

Other special types include arrays, ranges, and enumerated types. These provide additional flexibility for storing and querying data. Some of them are discussed below.

UUID

The UUID (Universally Unique Identifier) data type in PostgreSQL allows the storage of unique identifiers. UUIDs are 128-bit values and can be generated using various algorithms. It is used as a primary key or a unique identifier for records in a table. Thus ensuring each record has a globally unique identifier for easy referencing and identification.

Example:

In this example, we are creating a table named example_uuid. The table consists of two columns: id and uid. The uid column is of type UUID. The uid column is also specified as the PRIMARY KEY of the table, which means that its values must be unique and not null.

Output:

Array

PostgreSQL supports arrays as a data type for storing multiple values of the same type in a single column. Arrays can be used to represent lists, sets, or multidimensional data structures.

When working with arrays in PostgreSQL, you can use various array functions and operators to perform operations such as array concatenation, element extraction, and array intersection. This flexibility makes arrays a versatile choice for storing data.

Example:

In this example, we are creating a table named example_array. The table consists of four columns: id, name, numbers, and fruits. The numbers column is of type INTEGER[], which is an array of integers. The fruits column is of type TEXT[], which is an array of text values.

Output:

JSON

JSON stands for JavaScript Object Notation data. The JSON data type allows you to store and query JSON documents directly within the database.

In Postgres data types, the JSON type is used when dealing with complex data structures that do not fit well into traditional relational tables.

Example:

In this example, we are creating a table named example_json. The table consists of two columns: id and data. The data column is defined as JSON.

Output:

hstore

The hstore data type in PostgreSQL provides a key-value store within a single column. The hstore data type is beneficial when dealing with schema-less or evolving data structures.

It is used when you have varying attributes for different records in a table. For example, if you have a product table where each product can have different attributes. There you can use the hstore data type to store the attributes as key-value pairs.

Example:

The following SQL code creates a table called example_hstore with three columns: id, data_array, and data_hstore. The data_hstore column is defined as HSTORE.

Output:

But before creating the table example_hstore, run the following command to see if the hstore extension is installed in your database:-

If the query returns no results, it means the extension is not installed. In that case, execute the following command to install hstore:-