Data Types in SQL

What are data types?

Data types are classifications that define the kind of value a field (or column) can contain within a table in a database. It defines the type of data the field can hold, such as integer data, character data, monetary data, date and time data, binary strings, and so on.

Data types are important because they:

• Ensure that the data entered into fields is valid for the type of data the field intends to hold.

• Help maintain data integrity.

• Help improve the performance of the database when working with large sets of data.

Data types in PostgreSQL

PostgreSQL has a large number of different data types available for use.

A list of some of the most common data types is given below:

Boolean

The boolean data type can store two values: True or False. There’s also a third Unknown state, which is represented by Null. It’s used for storing unknown or non applicable values. The boolean data type takes one byte of storage. Boolean data types are often used with logical operators such as AND, OR, and NOT.

Character strings

These are collections of alphanumeric characters (letters, numbers, and special characters) and can be of fixed or variable length. Examples of character string data types in PostgreSQL include Char, Varchar, and Text.

• Character varying(n): Also known as Varchar, stores variable-length character strings of up to n characters in length. If no value is specified for n, the size is unlimited.

• Character(n): It’s similar to character varying but stores strings of exactly n characters in length. If the string is shorter in size than n characters, it will be padded with spaces to make it n characters long.

• Text: It stores character strings of unlimited length.

Numeric

Numeric data types are used for storing numeric values. They can store whole numbers, decimal numbers, and floating point numbers. This data type is often used for storing financial information or measurements. They can be used in mathematical operators such as +, -, *, and /.

Numeric data types in PostgreSQL include SmallInt, Integer, BigInt, Decimal, Numeric, Real, Double Precision, SmallSerial, Serial, and BigSerial.

• Smallint: This data type stores integers (between -32,768 and 32,767). It takes 2 bytes of storage.

• Integer: This data type stores whole numbers (between -2,147,483,648 and 2,147,483,647). It takes 4 bytes of storage.

• BigInt: This data type stores whole numbers (between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807). It takes 8 bytes of storage.

• Decimal or Numeric: It stores decimal numbers (numbers with a fractional component). The decimal data type can store up to 131,072 digits before and up to 16,383 digits after the decimal point. The Decimal and Numeric data types are interchangeable, so we can use either when we need to store a numeric value.

• Real: It stores floating point numbers (numbers with a fractional component) with an accuracy of 6 decimal places. It takes 4 bytes of storage.

• Double: It stores floating point numbers with an accuracy of 15 decimal places. It takes 8 bytes of storage.

• SmallSerial: It stores integers (between 1 and 32,767) that are automatically incremented by the database. It takes 2 bytes of storage.

• Serial: It stores integers (between 1 and 2,147,483,647) that are automatically incremented by the database. It takes 4 bytes of storage.

• BigSerial: It stores integers (between 1 and 9,223,372,036,854,775,807) that are automatically incremented by the database. It takes 8 bytes of storage.