What Is Record Data Type in PostgreSQL?

The record data type in PostgreSQL is a special data type that allows you to create a composite data structure. It is used to store a row or a tuple of values from various columns in a table. This data type is particularly useful when you want to group together related values and treat them as a single entity.

Creating a Record Data Type

To create a record data type, you need to define its structure by specifying the names and types of its fields. The syntax for creating a record data type is as follows:

CREATE TYPE typename AS (field1 datatype1, field2 datatype2, ..);

For example, let’s say we want to create a record data type called employee_type with three fields: name, age, and salary. Here’s how we can do it:

CREATE TYPE employee_type AS (name varchar(50), age integer, salary numeric(10, 2));

In the above example, we have defined the record data type employee_type, which consists of three fields: name, which has the data type varchar(50); age, which has the data type integer; and salary, which has the data type numeric(10, 2).

Using Record Data Type in Tables

The record data type can be used as a column in tables. Let’s consider an example where we have an “employees” table with three columns: “id”, “details”, and “department”.

The “details” column is of type employee_type. Here’s how we can create the table:

CREATE TABLE employees (
  id serial primary key,
  details employee_type,
  department varchar(50)
);

In the above example, we have created a table called “employees” with three columns. The “id” column is a serial primary key, the “details” column is of type employee_type, and the “department” column is of type varchar(50).

Inserting Values into a Record Data Type Column

To insert values into a record data type column, you can use the following syntax:

INSERT INTO table_name (col1, col2, .)
VALUES (value1, value2, .);

To insert values into the “employees” table we created earlier, you can do something like this:

INSERT INTO employees (details)
VALUES
  (('John Doe', 30, 5000.00)),
  (('Jane Smith', 35, 6000.00));

In the above example, we are inserting two rows into the “employees” table. Each row consists of a single value for the “details” column. The value is enclosed in double parentheses and follows the structure defined by the employee_type.

Selecting Values from a Record Data Type Column

To select values from a record data type column, you can use dot notation to access individual fields. Here’s an example:

SELECT details.name, details.age, details.salary
FROM employees;

In the above example, we are selecting the “name”, “age”, and “salary” fields from the “details” column of the “employees” table. The dot notation allows us to access these fields within the record data type.

Conclusion

The record data type in PostgreSQL provides a convenient way to group related values together and treat them as a single entity. By defining a structure for the record data type, you can create composite data structures that can be used as columns in tables. This allows for more flexibility in organizing and manipulating your data.