Creating Our First SQL Table


Monday, 29 Oct 2018 Kudzai Nyandoro

Why go through all the trouble of creating an SQL database when you can simply use a spreadsheet? One might argue that spreadsheets are great for visualizing and manipulating data, and not necessarily for storing data. As your spreadsheet gets larger and too complex to maintain, it might be time to find a different tool. This is where tools like PostgreSQL and SQL come to the rescue.

An Example of a flat file, Spreadsheet

Spreasheet

Data comes in different formats, shapes, and sizes. Unlike spreadsheets and other flat files, database management systems such as PostgreSQL require each column to have the same data type. Before we create our first SQL table, we need to learn a few things about maintaining the integrity of our data by learning about basic data types and constraints.

Basic SQL Data Types

1. NUMERIC: This datatype stores fractions e.g. numeric(9,3) holds 9 digits only, three of which come after the decimal.
2. INTEGER: Only stores whole numbers.
3. SERIAL: Used to create and store unique integer identifiers that cannot be null and they automatically increase.
4. DATE: Stores dates such as year, month, day.
5. TIMESTAMP: Stores time and date as in YYYY-MM-DD HH:MM: SS
6. CHAR(N): Char is for character and is used for storing character with a fixed length.
7. VARCHAR(N): Holds characters with a nonfixed length.

More on Data Types

Contraints

Constraints play an important role in ensuring high-quality and integrity of the data stored in a database. Constraints are optional and they can be applied at different levels of a database. Some places where constraints can be applied are columns in a table, tables in a database etc. Below are some examples of constraints.

DEFAULT:  A default values can be inserted.
NULL:  A NULL can be stored in this column.
NOT NULL: A NULL value cannot be stored in this column.
UNIQUE: All values stored in each row/tuple, of the database, must be unique.
PRIMARY KEY: This is a combination of the NOT NULL and UNIQUE constraints.

More on Constraints

Creating a Table

To create a table, first, start your PostgreSQL server by running one of the commands below, depending on the machine you're using.

If you're on AWS Cloud9: Start your PostgreSQL server if you've not yet started it.

$ sudo service postgresql start

If you're on a MacOS: Start your PostgreSQL server if you've not yet started it.

$ brew services start postgresql

If you're on an Ubuntu OS: Start your PostgreSQL server if you've not yet started it.

$ service postgresql start

Enter into the PostgreSQL database

$ psql postgres

View a list of all your databases

postgres=# \l

Connect to the rivers database

$ postgres=# \c rivers

Create a table for California Rivers

The SQL command below will create a table in our rivers database. The table will be named california_rivers and it will have columns, as listed on the left on the code block below and constraints as shown on the right side of the code block below. If we look at the spreadsheet above, we notice that each column has a name and this is what will see being created by the SQL code on the left side of the code block below.

Note these two dashes -- tell SQL to ignore the following code in the respective line, this is how comments are written in SQL. Examples of how to write SQL comments can be seen in the next three lines below.

-- Delete the table if it exists
-- DROP TABLE california_rivers;

-- Create a table for California rivers
CREATE TABLE california_rivers(
  river_id          SERIAL PRIMARY KEY,
  name              VARCHAR(50),
  class             VARCHAR(7),
  section           VARCHAR(50),
  put_in            VARCHAR(50),
  take_out          VARCHAR(50),
  distance_miles    INTEGER,
  season            VARCHAR(50),
  ideal_flow        INTEGER
);

Our First SQL Query

The SQL query below will select all the data that is currently in our rivers database. There should be nothing at this point. We'll add some data in our next tutorial. Compare the spreadsheet above to the SQL code returned after we run our first query.

rivers=# SELECT * FROM california_rivers;
 river_id | name | class | section | put_in | take_out | distance_miles | season | ideal_flow
----------+------+-------+---------+--------+----------+----------------+--------+------------
(0 rows)

I will be following this blog up with a screencast, so check out the Episodes index page for a new episode on creating an SQL table with PostgreSQL in the next few days. You can also learn more about creating tables here. Source code on GitHub.