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 - - 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:

Sample spreadsheet for storing users.

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.

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.

  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.

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 "". 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 "". 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 "" successfully.

[10:00:03] App 2 tries to create the new users with the email address "", 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…

  age INT,
  first_name TEXT,
  last_name TEXT,

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.

  age INT,
  first_name TEXT,
  last_name TEXT,

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 - .

Learn Web Development with Go!

Sign up for my mailing list and I'll send you a FREE sample from my course - Web Development with Go. The sample includes three chapters from the book, and over 2.5 hours of screencasts.

You will also receive notifications when I release new articles, along with other freebies that I only share with my mailing list.

Avatar of Jon Calhoun
Written by
Jon Calhoun

Jon Calhoun is a full stack web developer who also teaches about Go, web development, algorithms, and anything programming related. He also consults for other companies who have development needs. (If you need some development work done, get in touch!)

Jon is a co-founder of EasyPost, a shipping API that many fortune 500 companies use to power their shipping infrastructure, and prior to founding EasyPost he worked at google as a software engineer.

More in this series

This post is part of the series, Using PostgreSQL with Go.

Spread the word

Did you find this page helpful? Let others know about it!

Vote on Hacker News

Sharing helps me continue to create both free and premium Go resources.

Want to discuss the article?

See something that is wrong, think this article could be improved, or just want to say thanks? I'd love to hear what you have to say!

You can reach me via email or via twitter.

Recent Articles Mini-Series Tags About Me Go Courses

©2018 Jonathan Calhoun. All rights reserved.