Creating PostgreSQL databases and tables with raw SQL

In this tutorial we are going to walk through creating our first database and tables using raw SQL and PostgreSQL. While this won’t be an in-depth course, it will cover enough of the basics to help get you started using SQL in your applications.

Before we can get started, you need to make sure that you have PostgreSQL installed along with the command line tools. Specifically, we need to be able to run psql to access our PostgreSQL database, and we will be using the role/user postgres.

You can use a different other role if you prefer, but you will have to replace the role in a few of the examples below. Feel free to email me - jon@calhoun.io - if you are trying to do this and getting stuck. I am happy to help out.

Creating a Postgres database

The first thing we need to do is connect to Postgres with our postgres role. To do this we want to type the following into our terminal.

psql -U postgres

The -U postgres part is where we are specifying that we want to connect with the postgres role.

Next we want to go ahead and create our database. Creating a database is pretty simple using the CREATE DATABASE command.

CREATE DATABASE calhounio_demo;

Next we want to connect to our database. We do that by typing the following.

\c calhounio_demo

And that’s it. We have created and connected to the calhounio_demo database. Now lets go ahead and start creating a few tables to play with.

Creating SQL tables with Postgres

SQL tables are a little easier to understand if you think of them like creating a spreadsheet. When we define an SQL table we are creating a new spreadsheet and filling in the headers for each of the columns we are going to use.

For example, if we wanted to create a spreadsheet to store a list of users, we might start out with something like this:

When we want to add new users to our spreadsheet we would simply add another row to the spreadsheet and fill in a value for each of the columns.

A table in a PostgreSQL database works very similarly. When we want to add a new user to a table we would add a row to the table that stores all of the information that our table defines.

There are many things that technically make spreadsheets and SQL tables different, but the big ones we want to focus on are types and constraints. When we create an SQL table we will be using code that follows the format:

CREATE TABLE table_name (
  field_name TYPE CONSTRAINTS,
  field_name TYPE(args) CONSTRAINTS
);

We always declare the table name first, and then with each row we define a a field, or a column in the spreadsheet metaphor, and then we declare what type this field will be, followed by any constraints we have on the field.

Some types require additional arguments, which is why you will sometimes see parenthesis after the type with some values in it. We will discuss this in more detail in the next section.

After the types we see the constraints. These are rules that we can set on each column that the database will enforce. We will discuss these in the section after the next.

PostgreSQL data types

Types are basically a way of defining what kind of data you want to store in a column. For example, you might say “I only want to store integers in this column” or “I only want to store strings that are smaller than 140 characters in this column”.

PostgreSQL supports a wide variety of data types but for now we are going to stick with a few of the basic ones.

• int - This is used to store integers between -2147483648 and 2147483647.
• serial - This is used to store integers between 1 and 2147483647. The big difference between int and serial is that serial will automatically set a value if you don’t provide one, and the new value will always increase by 1. This is useful for things like the id column, where you want every row to have a unique value and are okay with the database deciding what value to use.
• varchar - This is like a string in Go or other programming languages, except we have to tell the database what the max length of any string we are storing is going to be.
• text - This is a type that is specific to PostgreSQL (and may not be available in all forms of SQL) but it is basically the same as varchar under the hood, but you don’t have to specify a maximum string length when you declare your field.

With these types we can go about creating some rules about what kind of data can be stored in each column when we declare our tables. For example, to create the users table we saw in a spreadsheet earlier we could use the following SQL.

CREATE TABLE users (
  id SERIAL,
  age INT,
  first_name VARCHAR(255),
  last_name VARCHAR(255),
  email TEXT
);

PostgreSQL constraints

As I mentioned before, constraints are rules that we can apply to different fields in our table. For example, we might want to ensure that every user in our database has a unique id, so we could use the UNIQUE constraint.

There are many constraints available in Postgres but for now we will mostly be using the following.

• UNIQUE - This ensures that every record in your database has a unique value for the field that is set to unique. For example, you might want every user to have a unique email address. It is important to note that Postgres is case sensitive, so jon@CALHOUN.io is not the same as jon@calhoun.io. You will need to account for this on your own when writing data to your database.
• NOT NULL - This ensure that every record in your database has a value for this field. When you don’t provide a value for a field the database will traditionally store null, but this prevents that from being valid.
• PRIMARY KEY - This constraint is similar to combining both UNIQUE and NOT NULL but it can only be used once on each table, and it will automatically result in a the creation of an index for this field. The index is used to make it faster to look up records by this field. For example, we typically set the id to be the primary key, and then we look up users by their id when we need to fetch them from the database.

Many of these constraints are things that you will typically be checking in your code, but it is important to be aware of how to set them in SQL as well because your code can run into race conditions where invalid data could be written to your database.

For example, imagine we had two programs writing to our database and the following occurred.

[10:00:00] App 1 checks to see if any users have the email "jon@calhoun.io". None do, so it looks like we can create a new user!

[10:00:01] App 2 checks to see if any users have the email "jon@calhoun.io". None do, so it looks like we can create a new user!

[10:00:02] App 1 creates the new users with the email address "jon@calhoun.io" successfully.

[10:00:03] App 2 tries to create the new users with the email address "jon@calhoun.io", but one already exists.

If we have a unique constraint in place, then at 10:00:03 when App 2 tries to create a new user our database will end up raising an error. On the other hand, if we don’t have this constraint we will end up with two users in our database with the same email address, which could be really problematic if our code only expects one to exist.

This isn’t something you need to worry about too much right now, but I only mention it to stress the importance of using database level constraints.

Using PostgreSQL constraints

We have talked about types and constraints, so now we need to see how to put these two together to create an SQL table where each field has both types and constraints. We will be continuing with the users table from before, but this time we will be adding the PRIMARY KEY constraint to the id column, and we will be adding both the NOT NULL and the UNIQUE constraints to the email field.

Our final code will look like…

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  age INT,
  first_name TEXT,
  last_name TEXT,
  email TEXT UNIQUE NOT NULL
);

To create this table we want to run this code inside of psql. If you don’t still have your terminal open with psql you should go ahead and open up your terminal and run the following.

psql -U postgres -d calhounio_demo

The -U will set the role to be postgres, and the -d will say that you want to connect to the calhounio_demo database.

Next we want to type in the SQL code from above.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  age INT,
  first_name TEXT,
  last_name TEXT,
  email TEXT UNIQUE NOT NULL
);

You should see the output CREATE TABLE. If you instead see ERROR: relation "users" already exists that means you already created a users table. To remove the table and recreate it you would need to run DROP TABLE users; before running the code above.

After that we are done. We have successfully connected to our database and created a table.

Up Next…

Next we will start inserting data into this table so that we can start querying it in the following article. If you want to move on to the next article you can find it here - Inserting records into a PostgreSQL database using SQL

You can also check out an overview of the entire series, along with links to each article in the series, by going here - .