PostgreSQL Introduction: How To Insert Data Into A PostgreSQL Database

By .

PostgreSQL databases can be created 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’.

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. In PostgreSQL, tables are created 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 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 (highlighted in purple). 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.

Facebook Comments

comments