Mastering PostgreSQL Databases: From Basics to Advanced/

...

/

Data Types in SQL

Data Types in SQL

Learn about different data types available in SQL, how to use them, and some examples.

We'll cover the following...

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 ...