SQL: Data Types

Data Types

Each field in a Table has a Type.

A Data Type is used to specify what kind of object that particular field will store.

When creating your own structures, using the right type for the right data is essential to prevent data loss and to work better with the information you have.

It is important to note that each database has different types.

They might be similar, but they are not exactly the same and you should be aware of this when dealing with different products like Oracle or MySQL, for instance.

I will list PostgreSQL Data Types in this article to give you an understanding of the main types.

If you are working with other databases, just check the documentation of each one and you should see lots of similarities to among them.

A few popular examples are:

Let’s learn about PostgreSQL Data Types.

Data Types in PostgreSQL

If you check the oficial documentation for PostgreSQL Data Types, you will see some very specific types for very specific cases.

Let’s keep things simple and use some Pareto’s 80/20 to show you the types you are going to use most of the time, they are:

  • Numeric Types: integer and floating-point
  • Character types: char, varchar and text
  • Boolean
  • Date/Time Types: date, time, timestamp, and interval

These types are very generic and you can use them to store numbers, text, and dates, which is enough for most use cases.

Numeric Types

You can work with either integers of float-pointing numbers.

Integer

For integers you can work with:

  • smallint: it stores 2 bytes and can store values in the range of -32768 to +32767.
  • integer: it stores 4 bytes and can store values in the range of -2147483648 to +2147483647
  • bigint: it can store 8 bytes and can store values in the range of -9223372036854775808 to +9223372036854775807

All of them have their serial counterparts, which is a specific type that automatically increments for every new row, it is very common for ID columns where you store values sequentially for each new record like 1, 2, 3, 4, 5 and so on.

For instance, the serial integer is:

  • serial: stores 4 bytes and ranges from 1 to 2147483647

Notice its range starts on 1, not on a negative number since it doesn’t make much sense to have and ID of, say, -20 for an employee, for instance.

Floating-point numbers

For floating-point numbers, in general you can choose to use numeric for most cases.

It supports a very large number of digits and it is easy to work with when you need to deal with monetary values or simply needs a good precision.

Character types

These types are used to store strings.

  • varchar: can store a string of the size you pre-define, and it does not pad spaces if the string to be stored is shorter than the declared length
  • char: can store a string of the size you pre-define, but it pads spaces if the string to be stored is shorter than the declared length
  • text: can store a string of any size, no need to predefine the number of characters

Boolean

The boolean type is by far the most simple one.

It stores 1 byte that represents true, false or null value for when you have an unknown state.

If you insert either of these in you database, PostgreSQL will understand them as true: true, yes, on, and 1.

If you insert either of these in you database, PostgreSQL will understand them as false: false, no, off, and 0.

Date/Time

You can basically store time, date, date and time simultaneously, and data/time with time zone:

  • timestamp: you can store the date and the time
  • date: you can store only the date
  • time: you can store only the time
  • timestamptz: is the same as timestamp with the addition of time zone
  • interval: you can store a period of time, a time interval