Creating Our First SQL Table
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
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.
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.
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
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.