Datatypes - (PostgreSQL Data Types)

PostgreSQL is a popular open-source relational database management system. It supports a wide range of data types, which can be used to store and manipulate different types of data. In this tutorial, we'll discuss the various datatypes available in PostgreSQL.

Syntax

CREATE TABLE table_name (
  column_name data_type [constraint_name],
  ...
);

Example

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  age INTEGER,
  salary NUMERIC(10, 2),
  hire_date DATE,
  is_manager BOOLEAN
);

Output

The above command creates a table named employees with six columns: id (serial), name (text), age (integer), salary (numeric), hire_date (date), and is_manager (boolean).

Explanation

• SERIAL: An auto-incrementing column that generates a unique value for the column with each new row.
• PRIMARY KEY: A constraint that specifies the unique identifier for each row in a table.
• TEXT: A variable-length string data type with a maximum length of 65535.
• INTEGER: A signed four-byte integer data type with a range of -2147483648 to +2147483647.
• NUMERIC(precision, scale): A fixed-point numeric data type with a user-specified precision and scale.
• DATE: A date data type that stores the year, month, and day.
• BOOLEAN: A boolean data type that stores true or false values.

Use

The data types available in PostgreSQL can be used to store and manipulate different types of data. They can be specified when creating a table or altering an existing column. It is important to choose the appropriate data type for each column to ensure efficient storage of data and to enable proper indexing and querying.

Important Points

• PostgreSQL supports many other data types beyond those shown in the example above.
• When choosing a data type, it is important to consider the range of values that the column will store and the operations that will be performed on the data.
• The choice of data type can have a significant impact on database performance and storage efficiency.

Summary

In this tutorial, we discussed the various datatypes available in PostgreSQL. We covered the syntax, example, output, explanation, use, and important points of PostgreSQL data types. With this knowledge, you can create tables and columns that properly store and manipulate the data in your PostgreSQL database.