By Nicholas Brown.
PostgreSQL databases contain relations (also referred to as ‘tables’) in which you store records. Each table contains rows and columns, and each column represents a field. For example, in a table named ‘batteries’, you could have a model column, type column, and capacity column. Each row would contain a record (information about a battery in this case). This format is very clean and conducive to data analysis. It also forms the basis of the relational model.
You can create a PostgreSQL database using the ‘createdb‘ command, followed by the database name. For example:
~$ createdb mydb
Note: A black background denotes a terminal/console command, and a gray background denotes code.
You can do that in either Windows or Linux and jump right into the creation of a table. For this example, we will name this PostgreSQL database ‘mydb’. You can also log into PostgreSQL first using the ‘psql’ command below and create your database by typing ‘CREATE DATABASE mydb‘ instead.
Log into your PostgreSQL installation and the ‘mydb’ database. One way to do this is by issuing the following command, assuming that it created the default user ‘postgres’ during installation:
~$ psql -U postgres mydb
Before you can start saving data, you will have to create a table in your PostgreSQL database. Let’s create a table and call it ‘batteries’ for now. You can create a PostgreSQL tale using the ‘CREATE TABLE‘ command. The following table has three columns: model, type, and capacity.
CREATE TABLE batteries ( model varchar(60), type varchar(60), capacity int );
Once you’ve pressed enter, PostgreSQL should have displayed ‘CREATE TABLE‘.
‘varchar(80)’ and ‘int’ are data type declarations. ‘int’ means integer, which is useful for storing numbers (not floating point numbers), and you can use the ‘numeric’data type to store decimals. varchar(60) means variable characters (up to 60 of them, as I defined in brackets). This is useful for fields containing a combination of numbers and letters (for example: strings) which you don’t intend to perform mathematical operations on.
Note: You don’t have to spread your statements out over multiple lines as shown above. You can also type commands on a single line as you see fit. Usually, typing on a single line is perfectly fine for brief statements such as the INSERT command below.
You can now insert a record (otherwise called a row) in this PostgreSQL table. Let’s start with a lithium-ion battery, and you must use single quotes, as it is a string. Also note that if you want to create a table or column name with uppercase letters in it, you must put the name in quotes. For example: If you were to name the ‘batteries’ table ‘Batteries’, you would have to type “Batteries” whenever performing a query on it.
INSERT INTO batteries VALUES ('NCR18650B', 'Lithium-Ion', 12);
The parameters in the INSERT command above are in the exact order that they were defined using the CREATE TABLE command earlier. This means that ‘NCR18650B’ is the battery model, ‘Lithium-Ion’ is the type, and ’12’ is the capacity in Watt-hours (Wh).
You can insert records using a different order, or omit some fields by using the following method. I’ll omit the ‘type’ field:
INSERT INTO batteries (model, capacity) VALUES ('IMR18650', 9);
All you need to do is state which fields you’re inserting the values into. View your table by typing: ‘SELECT * FROM batteries;‘ (this is a read operation/read query, which i’ll explain on another page). Your table should now look like this:
As you can see, you were able to omit the type field for the IMR18650. Let’s pretend this was done in error, and correct it using the ‘UPDATE’ and ‘WHERE’ statements in the next part of this tutorial, where will use the ‘date’ and ‘numeric’ data types. The date type helps us keep track of dates efficiently, and the numeric type enables us to store decimals.
In the next section, we’ll discuss updating records, dropping tables, and more data types.
When you’re ready, read about authentication so you can manage user roles and harden your PostgreSQL installation.
Also recommended: How to change PostgreSQL authentication method.