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 (
boolean, and date/time
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
this series first, before diving in to this course.
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.
Here are the links to the official PostgreSQL docs, which contain more details about the implementation of the different data types:
Enjoy learning about PostgreSQL? Sign up for our newsletter and get the latest tips from us each month.
Intro to 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
psql -U groot -h localhost workshop
You'll be prompted for the password (enter
Once logged in, run this
psql command to see information about the
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
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.