PostgreSQL - Just for Kids

Background Before We Get Started

What are databases?

Databases and computers go hand in hand. Grocery stores, banks, schools and even your favorite video games all use databases behind the computer application you see. Databases are collections of large amounts of data stored in tables. Tables look like grids with rows and columns. The central information used to look up rows are called keys. Tables usually have data related to each other, connected by those keys.

What is Postgres?

Postgres is a specific kind of database software for storing and retrieving data. What makes Postgres special is that it is open source software, which means it is written by a community of people and not one central company.

How can I work with Postgres?

To talk to Postgres you will use two things. One, is called psql and this is the terminal language used to talk to Postgres about itself.

The second thing is called SQL and this is how you retreive or change the data inside the Postgres database. SQL stands for Structured Query Language. This is the coding language humans and computers can use to talk to a database. SQL is the language used to talk to Postgres or any other SQL based database.

Using this tutorial

You'll see next to lines of code, there's a COPY command. Clicking that will copy that code block. Paste that into the window on the right where the cursor is and press Enter to run the command. These copy statements are a way to show you things that can be done. You can redo them or change things in the commands to make them your own.

Postgres only shows you part of a query to reduce the amount shown on a page. Sometimes when you run something, you’ll see -More-- at the very bottom. Use the arrow up key. Q will quit, space shows you a bit more, and R will show the rest.

Getting started with psql

We have loaded some data into this database for you. Let's try some simple psql commands.

This will list the tables we have

 Copy
\d

You will see three tables:

List of relations
Schema |         Name         | Type  |  Owner 
--------+----------------------+-------+----------
public | insect_names         | table | postgres
public | order_descriptions   | table | postgres
public | order_species_counts | table | postgres
(3 rows)

If we want to know more about what is inside one of the tables we could look at one more closely, here's we will look at the insect_names table.

 Copy
\d+ insect_names

Here we can see that we have many columns including:

common_name scientific_name Order family genus species author notes taxonomy_last_updated

Getting started with our insect sample data

Now if we want to look at the data inside insect_names, we will need some sql. If you don’t know how big your data is, you should never try to look at all of it. Here's a query to select data, but limit it to showing us 10 results. This is a good way to sample data without having to pull everything.

 Copy
SELECT * FROM insect_names LIMIT 10;

If you wanted to see more or less data, you could change the LIMIT operator and ask ask for any number of rows, say 2.

 Copy
SELECT * FROM insect_names LIMIT 2;

You can also just select certain parts of the data, * will give you all the rows, but this statement will just show you the common_names.

 Copy
SELECT common_name FROM insect_names LIMIT 10;

Finding things with WHERE

One thing that makes sql really powerful is being able to use filters to find the data you want. The WHERE filter is really easy to use, and you tell the database to give you information where certain information matches what you're looking for. Here in this example, we are selecting the whole row, from insect names, where the common name is like worm.

The ILIKE is an operator that will help you find names no matter if they are in lowercase or upper case. The % around the worm make sure that worm could be in any part of the common name.

 Copy
SELECT * FROM insect_names WHERE common_name ILIKE '%worm%';

Want to find a bug named after you? This sql query asks for a bug with the name like Franklin.

 Copy
SELECT * FROM insect_names WHERE common_name ILIKE '%franklin%';

Now paste it again and see if you can find our name or someone you know.

Updating data

Let's say for the sake of learning sql today that you want to update the name of a bug to be your name. I'm going to change the name of 'Franklin bumble bee' to 'Lizzie bumble bee'. This is something we do with an UPDATE statement.

 Copy
UPDATE insect_names SET common_name ='Lizzie bumble bee' 
WHERE scientific_name = 'Bombus franklini Frison';

Ok, now check that your name is there, this is a select statement if my name is Lizzie:

 Copy
SELECT * FROM insect_names WHERE common_name ILIKE '%lizzie%';

Inserting & Adding data

Another important thing you can do with data in SQL is create new things. You create a new row of data in your table with an INSERT statement. What if we create a new kind of bug named after our favorite food. Let's say we want to create a new stick bug. Stick bugs are part of the order 'Phasmatodea', family 'Diapheromeridae', and genus 'denticrus'. We'll create a row that creates:

• common_name: pizza bug
• scientific_name: Megaphasma pizzaii
• Order 'Phasmatodea', family 'Diapheromeridae', and genus 'denticrus'
• Species pizzaii
• Author Lizzie (or your name there)
• notes, this is a test insert statement - not a real insect

 Copy
INSERT INTO insect_names (common_name, 
scientific_name, 
"Order", 
family, 
genus, 
species, 
author, 
notes)
VALUES ('pizza bug', 
'Megaphasma pizzaii', 
'Phasmatodea', 
'Diapheromeridae', 
'denticrus', 
'pizzaii', 
'Lizzie', 
'this is a test insert statement -  not a real insect');

Ok, now we'll check that our new bug is there:

 Copy
SELECT * FROM insect_names WHERE common_name ILIKE '%pizza%'

You might have noticed when you pasted that query that your code went across several lines, like this

postgres=# INSERT INTO insect_names (common_name,
postgres(# scientific_name,
postgres(# "Order",
postgres(# family,
postgres(# genus,
postgres(# species,
postgres(# author,
postgres(# notes)
postgres-# VALUES ('pizza bug',
postgres(# 'Megaphasma pizzaii',
postgres(# 'Phasmatodea',
postgres(# 'Diapheromeridae',
postgres(# 'denticrus',
postgres(# 'pizzaii',
postgres(# 'Lizzie',

When you’re in a Postgres terminal the semicolon ; is what ends a statement. If you forget it, you can add it on the next line.

Joining different tables

One of the things that makes databases really powerful is that you can connect lots of different tables together. Using data from more than one table is called a join and you'll need something that's common in both tables so the database knows how to relate things to each other.

Let's look at the other tables in our database with:

 Copy
\dt

We've been talking mostly about the insect_names table but there's two more in there, order_descriptions and order_species_counts. Let's take a peek at them.

 Copy
SELECT * from order_species_counts limit 2;

 Copy
SELECT * from order_descriptions limit 2;

So I can see that order_descriptions is telling me the Order names and what kinds of insects are in each group. So let's try to find the Order for butterflies.

 Copy
SELECT * FROM order_descriptions 
WHERE examples ILIKE '%butterflies%';

And using that Order name, Lepidoptera, order_species_counts will tells me how many species there are.

 Copy
SELECT extant_species_described 
FROM order_species_counts 
WHERE "Order" ='Lepidoptera';

To combine these two queries into one with a join it would look like this:

 Copy
SELECT DISTINCT extant_species_described 
FROM order_species_counts o 
INNER JOIN order_descriptions d 
ON (o."Order" = d."Order") 
WHERE d.examples ILIKE '%butterflies%';

I am asking about the same count in the species number table. But since I joined it with the order_descriptions table, I can search and get the results all together.

If you look at the query, you’ll see that we give each table an abbreviation order_species_counts o and order_descriptions d and then when we talk about that data from that table, we use the abbreviation, d.examples.

If I want to look up the common names, type_of_mouth_parts and metaphors type for all insects with a common name like butterfly, I can run this. You see how we're able to pull one field, common_name, and two other fields from a second table.

 Copy
SELECT i.common_name, o.type_of_mouth_parts, o.metamorphosis 
  FROM order_descriptions o 
  INNER JOIN insect_names i 
  ON (o."Order" = i."Order") 
	WHERE i.common_name ilike '%butterfly%';

Side note about the word “Order”: You might be wondering why when I talked to sql about "Order" I have to put that in quotes? We’ll, order is also a special sql operation for ordering you data in a specific way. We have to quote it so Postgres knows we’re talking about the data and not the operation.

Counting data and rows

Counting data in sql usually means you are counting the number of rows. So if I want to see how many entries I have in my insect_names table (note, this is not all insects in the whole world, its just a subset) I would do:

 Copy
SELECT COUNT (*) FROM insect_names;

If you want to use the database to add using mathematics, you will use the SUM function. You can use the SUM function to total up different data in rows. For example, if I want to see the total number of species across all the orders is my order_species_counts table, that would be something like this:

 Copy
SELECT SUM(extant_species_described) AS total_species 
  FROM order_species_counts;

You can also combine the SUM with other things we have learned like filtering with WHERE and ILIKE and a join. Here's a query to find the number of species that are either dragonflies or butterflies.

 Copy
SELECT SUM(s.extant_species_described) AS total_species 
  FROM order_species_counts s 
  INNER JOIN order_descriptions o 
  ON (s."Order" = o."Order") 
  WHERE o.examples iLIKE '%dragonflies%' 
  OR o.examples ilike '%butterflies%';

Learn more SQL & PostgreSQL

Thanks for sticking with us through this introductory tutorial! You're welcome to stay around and play with this data. It’s running for you, and only you, in your web browser. You can come back at anytime.

A fun next step to take in our playground would be working some of the Advent of Code Tutorials. They solve some puzzles related to the Christmas holidays. You can read about the problems they're trying to solve and follow along with the solutions. The first one starts here.