Lesson 002: Creating Tables in PostgreSQL

For this post we’ll be using the files in the Lesson_002 folder in the learning-data-science repository. If you have not already forked the learning-data-science repository, and cloned the fork to your computer, please do so now following the instructions provided in the post Learning Data Science. If you have previously forked and cloned the repository, but don’t have the files for this lesson, then later in this post I’ll provide instructions for updating your fork.

If you are not familiar with version control using Git and GitHub, I recommend going back and working through the hands on tutorials in my series on version control starting with “Version Control: An Overview.”

PostgreSQL Tables

Tables are used to store data in a relational database. In a database table rows represent data records, columns represent data attributes, and the cells within the table contain the data values for each data record and attribute. Figure 1 shows an example of a simple database table model illustrating the concepts of data records, attributes, and values.

The columns in a table are each assigned a name, and are defined by their data type (e.g., integer) and database constraints. Information about data types and database constraints is stored in the database as table and column metadata.

Data Types

Data are classified based on the values they can take on (e.g., integer). The different classes of data are referred to as data types. Each column within a table is assigned a column name and a data type based on the data attributes that the column is designed to contain. Data types, by enforcing what kind of data can be stored in a column, are one way that relational database ensure data integrity.

For instance, imagine you have a database column that is designed to contain an integer value. Were you to accidentally enter text, for instance the word “one”, into that field the database would stop you from doing this and warn you that the data entered doesn’t match the data type for that column. Without the requirement to set a data type you wouldn’t get a warning. Let’s say that later on you wanted to calculate an average and standard deviation from the data in that column, well you would get an error because you obviously can’t perform math on text values. PostgreSQL has a variety of data types ranging from text to numeric, arrays to monetary data types, and from data/time to geometric data types. The most common data types you’ll use are described briefly below:

• Text: Character strings of unlimited number of characters.
• Character varying: Character strings up to a predetermined number of characters.
• Integer: Whole numbers.
• Numeric: Decimal numbers of a predetermined precision.
• Boolean: True/False data.
• Date: Calendar date in year-month-day format.
• Serial: Auto-incrementing integer

Database Constraints

Referential integrity is a database construct in which relationships between data elements are structured and enforced through the use of database constraints. Database constraints are a fundamental and important element of a relational database and help ensure data integrity. I’ll cover database constraints in more detail in a future post. However, for the purpose of this post here is a quick overview of primary and unique keys.

• Primary keys: Defines a column or group of columns that are used as a unique identifier within and between tables. Null values are not allowed in any columns included in a primary key. Single column primary keys require that the values in each row are unique, while multi-column primary keys require that the combination of values across all columns in a row that are part of the key are unique.
• Unique keys: Ensures that the values in each row of a column, or across a set of columns are unique.

Database constraints are at the core of a good relational database as they define the rules by which the data in different tables and columns relate to each other. You may find yourself frustrated at times when working in a database with proper referential integrity enforced using database constraints. This is because the database will block you from performing some action that you may want to complete if that action goes against the database constraints. When this happens take a deep breath and bear in mind that this is exactly why database constraints exist. From my own personal experience I know that the few minutes it takes to perform a database action in line with the constraints will save you hours later on tracking down and fixing database issues were there no constraints in place. I’ll provide some practical examples of this in future posts.

Creating a PostgreSQL Table

There are several ways to create a PostgreSQL table. To begin we’ll use a SQL create table script, and then I’ll run through how to use the Create Table tool in pgAdmin. However, to ensure you have the most up-to-date files for this lesson, let’s first review how to update your fork of the learning-data-science repository.

Updating your Fork of the learning-data-science Repository

If you have not already forked the learning-data-science repository, and cloned the fork to your computer, please do so now following the instructions provided in the post Learning Data Science. After you’ve completed this you will have the files you need for this lesson, and you can skip the next section and pick up the lesson again below starting at “Creating a Table using SQL.”

However, if you have previously forked and cloned the repository, but don’t have the files for this lesson (i.e., Lessons/Lesson_002), then please follow the instructions below to update your fork with the latest lesson files:

Setting the Upstream Repository

First, navigate to the learning-data-science repository on your computer and open a terminal (MacOS) or GitBash(Windows). Next, list the currently configured remote repository for your fork using the following command:

git remote -v

You should see something like the following returned:

   origin  https://github.com/YOUR_USERNAME/learning-data-science.git (fetch)
   origin  https://github.com/YOUR_USERNAME/learning-data-science.git (push) 

Next, specify the original learning-data-science repository as the upstream repository that will be synced with the fork. Use the following command:

git remote add upstream https://github.com/elfinwood-data-sci/learning-data-science.git

Now verify the new upstream repository using the “git remote -v” command (as above). You should see something like the following returned:

origin    https://github.com/YOUR_USERNAME/learning-data-science.git (fetch)
origin    https://github.com/YOUR_USERNAME/learning-data-science.git (push)
upstream  https://github.com/elfinwood-data-sci/learning-data-science.git (fetch) 
upstream  https://github.com/elfinwood-data-sci/learning-data-science.git(push)

Syncing your Fork

The next stage is to sync your fork with the original learning-data-science repository. First, fetch the files from the upstream repository using the following command:

git pull upstream master

After you hit Enter if you get something similar to the following message then your files were already up-to-date with the most recent files in the elfinwood-data-sci/learning-data-science repository.

From https://github.com/elfinwood-data-sci/learning-data-science
 branch            master     -> FETCH_HEAD
Already up-to-date.

If you do not receive the above message, but instead a text editor opens and you are asked to add a merge message, then your files were not up to date. Depending on your operating system follow the instructions below to write and save a merge message:

After you’ve saved the merge message you should see something like the following in your terminal where A_NEW_FILE.txt represents one or more new lesson files or folders pulled in from the elfinwood-data-sci/learning-data-science repository.

remote: Enumerating objects: 4, done.
remote: Counting objects: 100% (4/4), done.
remote: Compressing objects: 100% (3/3), done.
remote: Total 3 (delta 1), reused 0 (delta 0), pack-reused 0
Unpacking objects: 100% (3/3), done.
From https://github.com/elfinwood-data-sci/learning-data-science
 *branch            master     -> FETCH_HEAD
 4c78e88..a5c6b45  master     -> upstream/master
Merge made by the 'recursive' strategy.
 A_NEW_FILE.txt | 1 +
 1 file changed, 1 insertion(+)
 create mode 100644 A_NEW_FILE.txt 

Pushing to your Fork on GitHub

Now that you’ve updated the files in your local repository, the next step is to push the changes to your remote fork so as to keep it up to date. To push to your remote fork use the following command:

git push origin master

You should see something like the following returned:

Counting objects: 2, done.
Delta compression using up to 4 threads.
Compressing objects: 100% (2/2), done.
Writing objects: 100% (2/2), 326 bytes | 326.00 KiB/s, done.
Total 2 (delta 1), reused 0 (delta 0)
remote: Resolving deltas: 100% (1/1), completed with 1 local object.
To https://github.com/YOUR_USERNAME/learning-data-science.git
    07852de..8a4d5aa  master -> master

At this point you should have the most recent lesson files in your local and remote repositories. You are now ready to begin this lesson.

Creating a Table using SQL

We’ll start by creating a table in the domains schema that we’ll call the “dom_study” table. The name of the table is designed to be informative as it includes the prefix “dom_” which indicates that it is a domain table, followed by “study” indicating the purpose of the table is to store information about scientific studies. The data collected as part of the studies will be stored in other related tables in this database. The table design will include a column with a serial data type (auto-incrementing integer) that will be used as the primary key. Columns with a serial data type are each associated with their own “sequence” which defines the settings and auto-incrementing behavior. So, before we can create the table we need to create a sequence using the below steps:

1. First open pgAdmin, click on my_first_database to connect to it, and open a Query Tool window.
2. Click on the Open File tool and navigate to the clone of your fork of the learning-data-science repository.
3. Open the create_dom_study_serial_seq.sql file under Lessons/Lesson_002. The sql file will open in the query tool (Figure 2).
4. Click the Execute button to run the script and create the sequence.
5. You should now see that there is a new sequence called “dom_study_serial_seq” in the domains schema (Figure 3).

Lastly, lines 12 and 13 include the ALTER SEQUENCE command which is used here to specify the owner of the sequence, in this case the user postgres.

Now that we’ve created a sequence for the column in our table with a serial data type, let’s create the domains.dom_study table following these steps:

1. Open a Query Tool window in pgAdmin
2. Click on the Open File tool and navigate to the clone of your fork of the learning-data-science repository.
3. Open the create_dom_study_tbl.sql file under Lessons/Lesson_002. The sql file will open in the query tool (Figure 4).
4. Click the Execute button to run the script and create the sequence.
5. You should now see that there is a new dom_study table in the domains schema (Figure 5).

The last part of the script on lines is the statement for assigning the ownership of the table to the user postgres. Did you notice in Figure 4 that the word “label” is highlighted in the color magenta? In fact there are a number of words that are highlighted this way, for instance CREATE, TABLE, NOT, and DEFAULT, among others. These words are highlighted because they are SQL keywords. Keywords are essentially words that have special meaning in the SQL language either because they are used as the name for an internal database object or as a SQL command. Keywords are either reserved or unreserved. The most important difference between the 2 is that reserved key words can’t be used as the name of a database object, while unreserved words can. PostgreSQL uses a slightly modified version of the 2016 SQL standard list of key words. You can view a list of the keywords and their reserved/unreserved designation in Appendix C of the PostgreSQL documentation, or by using the following select query in the pgAdmin Query tool. I’ll cover select queries in more detail in a future post.

SELECT *
FROM pg_get_keywords()
ORDER BY 1;

In the columns tab you can edit the properties of any column by clicking on the “edit row” button (i.e., pencil icon) on the far left side of each respective row. The constraints tab (Figure 9 upper right), as the name suggests, holds the information about the database constraints. For instance, the primary and unique keys that we created in the create table script.

Now that we’ve explored a little in pgAdmin next I’ll describe what data the columns are intended to store. Recall that the dom_study table is intended to store metadata about scientific studies, the data from which will be stored in other related tables in this database.

• unique_id is an auto-incrementing integer (i.e., a “serial” datatype) that will serve as a unique identifier for each row.
• sort_order has a numeric data type and will be used to enter the sort order for each data_value, for instance the order the values will appear in a report table. The third column.
• data_value is character varying with a maximum length of 32 characters and will be used to store the data values that will entered into the data tables.
• label is character varying with a max length of 255 characters and will be used to store the descriptive titles for each data value.
• definition is text and will be used to store the definition or description of each data value.
• study_start_date and study_end_date will be used to store the start and end dates of the study, respectively. For instance, the date the funding for the study became available (study_start_date), and the end date of the contract or grant that funded the study (study_end_date).
• note is a free-form text field that can be used to store notes (i.e., unstructured data) about each record in the table.

Recall that in SQL you can string together as many statements as you’d like into a single script so long as the statements are separated by semicolons. Thus, we could have included the SQL code from the add_dom_study_comments.sql script in the create_dom_study_tbl.sql script to begin with, for instance starting on line 21. This would have allowed us to run the create table script and column comments script all with one click of the Execute button.

Create – Table Tool

1. In the domains schema, right click on Tables, then click on Create, and then on Table… (Figure 10). The Create – Table Dialog will open (Figure 11).
2. To create a table, enter the necessary information into the General (Figure 11 left), Columns (Figure 11 center), and Constraints (Figure 11 right) table, then click Save.

Create Table Script Tool

Now go to pgAdmin and navigate to and click on the schemas node in my_first_database. Click on the domains schema, then on Tables, and finally on dom_study (Figure 12, left side). On the right side of the screen click on the SQL tab. You should then see the SQL that was used to create the table (Figure 12, right side).

Recommended Reading

As a supplement to this post, I encourage you to read the GitHub documentation on configuring a remote for a fork, syncing a fork, and pushing commits to a remote repository; and the PostgreSQL documentation on sequences, comments, and creating tables.

Next Time on Elfinwood Data Science Blog

In this post, we created a sequence and a table, and added comments to columns in a table all using SQL. We explored various ways to interact with a table in pgAdmin, and discussed using the create table dialog and create scripts for creating tables. In the next post we’ll insert data into the dom_study table and learn how to delete rows from a table.

If you like this post then please consider subscribing to this blog (see below) or following me on social media.If you’d like to follow this blog, but you don’t want to follow by subscribing through WordPress then a couple of options exist. You can “Like” the Elfinwood Data Science Blog Facebook Page or send me your email using the form on the Contacts Page and I’ll add you to the blog’s email list.

---

Follow My Blog

Copyright © 2021, Aaron Wells