Getting Started with PostgreSQL

10 min read

In this article by Ibrar Ahmed, Asif Fayyaz, and Amjad Shahzad, authors of the book PostgreSQL Developer’s Guide, we will come across the basic features and functions of PostgreSQL, such as writing queries using psql, data definition in tables, and data manipulation from tables.

(For more resources related to this topic, see here.)

PostgreSQL is widely considered to be one of the most stable database servers available today, with multiple features that include:

A wide range of built-in types
MVCC
New SQL enhancements, including foreign keys, primary keys, and constraints
Open source code, maintained by a team of developers
Trigger and procedure support with multiple procedural languages
Extensibility in the sense of adding new data types and the client language

From the early releases of PostgreSQL (from version 6.0 that is), many changes have been made, with each new major version adding new and more advanced features. The current version is PostgreSQL 9.4 and is available from several sources and in various binary formats.

Writing queries using psql

Before proceeding, allow me to explain to you that throughout this article, we will use a warehouse database called warehouse_db. In this section, I will show you how you can create such a database, providing you with sample code for assistance. You will need to do the following:

We are assuming here that you have successfully installed PostgreSQL and faced no issues. Now, you will need to connect with the default database that is created by the PostgreSQL installer. To do this, navigate to the default path of installation, which is /opt/PostgreSQL/9.4/bin from your command line, and execute the following command that will prompt for a postgres user password that you provided during the installation:
```
/opt/PostgreSQL/9.4/bin$./psql -U postgres
Password for user postgres:
```
Using the following command, you can log in to the default database with the user postgres and you will be able to see the following on your command line:
```
psql (9.4beta1)
Type "help" for help
postgres=#
```
You can then create a new database called warehouse_db using the following statement in the terminal:
```
postgres=# CREATE DATABASE warehouse_db;
```
You can then connect with the warehouse_db database using the following command:
```
postgres=# c warehouse_db
```
You are now connected to the warehouse_db database as the user postgres, and you will have the following warehouse_db shell:
```
warehouse_db=#
```

Let’s summarize what we have achieved so far. We are now able to connect with the default database postgres and created a warehouse_db database successfully. It’s now time to actually write queries using psql and perform some Data Definition Language (DDL) and Data Manipulation Language (DML) operations, which we will cover in the following sections.

In PostgreSQL, we can have multiple databases. Inside the databases, we can have multiple extensions and schemas. Inside each schema, we can have database objects such as tables, views, sequences, procedures, and functions.

We are first going to create a schema named record and then we will create some tables in this schema. To create a schema named record in the warehouse_db database, use the following statement:

warehouse_db=# CREATE SCHEMA record;

Creating, altering, and truncating a table

In this section, we will learn about creating a table, altering the table definition, and truncating the table.

Creating tables

Now, let’s perform some DDL operations starting with creating tables. To create a table named warehouse_tbl, execute the following statements:

warehouse_db=# CREATE TABLE warehouse_tbl
(
warehouse_id INTEGER NOT NULL,
warehouse_name TEXT NOT NULL,
year_created INTEGER,
street_address TEXT,
city CHARACTER VARYING(100),
state CHARACTER VARYING(2),
zip CHARACTER VARYING(10),
CONSTRAINT "PRIM_KEY" PRIMARY KEY (warehouse_id)
);

The preceding statements created the table warehouse_tbl that has the primary key warehouse_id. Now, as you are familiar with the table creation syntax, let’s create a sequence and use that in a table. You can create the hist_id_seq sequence using the following statement:

warehouse_db=# CREATE SEQUENCE hist_id_seq;

The preceding CREATE SEQUENCE command creates a new sequence number generator. This involves creating and initializing a new special single-row table with the name hist_id_seq. The user issuing the command will own the generator. You can now create the table that implements the hist_id_seq sequence using the following statement:

warehouse_db=# CREATE TABLE history
(
history_id INTEGER NOT NULL DEFAULT nextval('hist_id_seq'),
date TIMESTAMP WITHOUT TIME ZONE,
amount INTEGER,
data TEXT,
customer_id INTEGER,
warehouse_id INTEGER,
CONSTRAINT "PRM_KEY" PRIMARY KEY (history_id),
CONSTRAINT "FORN_KEY"
FOREIGN KEY (warehouse_id)
REFERENCES warehouse_tbl(warehouse_id)
);

The preceding query will create a history table in the warehouse_db database, and the history_id column uses the sequence as the default input value.

In this section, we successfully learned how to create a table and also learned how to use a sequence inside the table creation syntax.

Altering tables

Now that we have learned how to create multiple tables, we can practice some ALTER TABLE commands by following this section. With the ALTER TABLE command, we can add, remove, or rename table columns.

Firstly, with the help of the following example, we will be able to add the phone_no column in the previously created table warehouse_tbl:

warehouse_db=# ALTER TABLE warehouse_tbl
ADD COLUMN phone_no INTEGER;

We can then verify that a column is added in the table by describing the table as follows:

warehouse_db=# d warehouse_tbl
           Table "public.warehouse_tbl"            
     Column     |         Type         | Modifiers
----------------+------------------------+-----------
warehouse_id  | integer               | not null
warehouse_name | text                   | not null
year_created   | integer               |
street_address | text                   |
city           | character varying(100) |
state           | character varying(2)   |
zip             | character varying(10) |
phone_no       | integer               |
Indexes:
"PRIM_KEY" PRIMARY KEY, btree (warehouse_id)
Referenced by:
TABLE "history" CONSTRAINT "FORN_KEY"FOREIGN KEY  (warehouse_id) REFERENCES warehouse_tbl(warehouse_id)
 TABLE  "history" CONSTRAINT "FORN_KEY" FOREIGN KEY (warehouse_id)  REFERENCES warehouse_tbl(warehouse_id)

To drop a column from a table, we can use the following statement:

warehouse_db=# ALTER TABLE warehouse_tbl

DROP COLUMN phone_no;

We can then finally verify that the column has been removed from the table by describing the table again as follows:

warehouse_db=# d warehouse_tbl
           Table "public.warehouse_tbl"            
     Column     |         Type         | Modifiers
----------------+------------------------+-----------
warehouse_id   | integer               | not null
warehouse_name | text                   | not null
year_created   | integer               |
street_address | text                   |
city           | character varying(100) |
state           | character varying(2)   |
zip             | character varying(10) |
Indexes:
"PRIM_KEY" PRIMARY KEY, btree (warehouse_id)
Referenced by:
TABLE "history" CONSTRAINT "FORN_KEY" FOREIGN KEY  (warehouse_id) REFERENCES warehouse_tbl(warehouse_id)
 TABLE  "history" CONSTRAINT "FORN_KEY" FOREIGN KEY (warehouse_id)  REFERENCES warehouse_tbl(warehouse_id)

Truncating tables

The TRUNCATE command is used to remove all rows from a table without providing any criteria. In the case of the DELETE command, the user has to provide the delete criteria using the WHERE clause. To truncate data from the table, we can use the following statement:

warehouse_db=# TRUNCATE TABLE warehouse_tbl;

We can then verify that the warehouse_tbl table has been truncated by performing a SELECT COUNT(*) query on it using the following statement:

warehouse_db=# SELECT COUNT(*) FROM warehouse_tbl;
count
-------
     0
(1 row)

Inserting, updating, and deleting data from tables

In this section, we will play around with data and learn how to insert, update, and delete data from a table.

Inserting data

So far, we have learned how to create and alter a table. Now it’s time to play around with some data. Let’s start by inserting records in the warehouse_tbl table using the following command snippet:

warehouse_db=# INSERT INTO warehouse_tbl
(
warehouse_id,
warehouse_name,
year_created,
street_address,
city,
state,
zip
)
VALUES
(
1,
'Mark Corp',
2009,
'207-F Main Service Road East',
'New London',
'CT',
4321
);

We can then verify that the record has been inserted by performing a SELECT query on the warehouse_tbl table as follows:

warehouse_db=# SELECT warehouse_id, warehouse_name, street_address               FROM warehouse_tbl;
warehouse_id | warehouse_name |       street_address        
---------------+----------------+-------------------------------
>             1 | Mark Corp     | 207-F Main Service Road East
(1 row)

Updating data

Once we have inserted data in our table, we should know how to update it. This can be done using the following statement:

warehouse_db=# UPDATE warehouse_tbl
SET year_created=2010
WHERE year_created=2009;

To verify that a record is updated, let’s perform a SELECT query on the warehouse_tbl table as follows:

warehouse_db=# SELECT warehouse_id, year_created FROM               warehouse_tbl;
warehouse_id | year_created
--------------+--------------
           1 |         2010
(1 row)

Deleting data

To delete data from a table, we can use the DELETE command. Let’s add a few records to the table and then later on delete data on the basis of certain conditions:

warehouse_db=# INSERT INTO warehouse_tbl
(
warehouse_id,
warehouse_name,
year_created,
street_address,
city,
state,
zip
)
VALUES
(
2,
'Bill & Co',
2014,
'Lilly Road',
'New London',
'CT',
4321
);
warehouse_db=# INSERT INTO warehouse_tbl
(
warehouse_id,
warehouse_name,
year_created,
street_address,
city,
state,
zip
)
VALUES
(
3,
'West point',
2013,
'Down Town',
'New London',
'CT',
4321
);

We can then delete data from the warehouse.tbl table, where warehouse_name is Bill & Co, by executing the following statement:

warehouse_db=# DELETE FROM warehouse_tbl
WHERE warehouse_name='Bill & Co';

To verify that a record has been deleted, we will execute the following SELECT query:

warehouse_db=# SELECT warehouse_id, warehouse_name
FROM warehouse_tbl
WHERE warehouse_name='Bill & Co';
warehouse_id | warehouse_name
--------------+----------------
(0 rows)

The DELETE command is used to drop a row from a table, whereas the DROP command is used to drop a complete table. The TRUNCATE command is used to empty the whole table.

Summary

In this article, we learned how to utilize the SQL language for a collection of everyday DBMS exercises in an easy-to-use practical way. We also figured out how to make a complete database that incorporates DDL (create, alter, and truncate) and DML (insert, update, and delete) operators.