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.
How To Save Data In A PostgreSQL Table
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.