Difficulty: beginner
Estimated Time: 10 minutes

Data Types in PostgreSQL: An Introduction

In these next few scenarios, we'll focus on data types in PostgreSQL. From the previous scenarios, we've learned how to log in to a Postgres database, create data tables, and even add values to those tables. You'll recall that when we create a table, we have to define what kind of data each column will store.

If you have programming experience, the concept of data types isn't new. Data types in relational database management systems (DBMS) such as Postgres follow a lot of the same general principles, so this course will serve mostly as a refresher.

Since this is an introductory course, we'll focus on a few of the most common data types in Postgres: character types (char, varchar, and text), numeric (including integer and decimal/numeric), boolean, and date/time (including date and timestamp). With that said, we'll also introduce some concepts that are pertinent to Postgres and databases in general.

Using the Command Line

This course will use psql to navigate and do updates in the database. If you're not familiar with psql, we'd encourage you to check out the preceding introductory course on psql in this series first, before diving in to this course.

Conclusion

In this course, we dove a little bit into some of Postgres' more basic built-in data types: character, numeric, boolean, and date/time.

It's important to have planned out your database stucture first before creating new tables and adding data to them. This can save you time and effort further down the line.

Documentation

Here are the links to the official PostgreSQL docs, which contain more details about the implementation of the different data types:

  1. Character types
  2. Numeric types
  3. Boolean type
  4. Date/time types

Enjoy learning about PostgreSQL? Sign up for our newsletter and get the latest tips from us each month.

Intro to Data Types

Step 1 of 7

Data Types

So what about data types?

Take Excel: when you create a new spreadsheet, you're starting completely from scratch. You don't even have to worry about data types as you begin adding values to the spreadsheet (Excel might make educated guesses). However, once you need to do any type of calculation, function, or processing, formatting the cells or columns to the correct data type becomes necessary.

PostgreSQL and other relational database management systems work a little bit differently in that when you create a new table, you must specify the data type associated with each column. You can change the data type at a later time (towards the end of this course we'll see how to do so, and consider its implications), but the data type must be set initially each time a new table is created or a new column added to a table.

Check data types in a table

It's good practice to take a glance around whenever you start working with a database that's new to you. You'll have a better idea of how to work with the data, and it helps with troubleshooting issues later.

In the terminal, you can quickly check data types in a table by logging in to Postgres using psql:

psql -U groot -h localhost workshop

You'll be prompted for the password (enter password).

Once logged in, run this psql command to see information about the employee table:

\d employee

You should see some metadata for the groot.employee table, where Type indicates the data type held in that column.

And if you display the contents of the table, you should see data in each column that matches up with the designated data type:

SELECT * FROM employee;

(Since this is a small table, a SELECT * is fine, but it might make more sense to use LIMIT for any potentially large tables.)

Postgres currently has over 40 built-in data types. There is also the ability to create user-defined data types, which will not be discussed in this intro course.