Hands-on SQL in PostgreSQL

In this article you will learn about SQL support in PostgreSQL as well as learn how commands are typically entered using the PostgreSQL interactive terminal – psql. The hands-on part of the SQL deals with CRUD, viewing data etc. as some of the basic SQL commands in PostgreSQL. PostgreSQL is an advanced ORDBMS package and can be thought of as an open source rival to none other than Oracle. There are many intricate database concepts incorporated in this system, but for the sake of brevity and simplicity we’ll stick to hands-on SQL and its related ideas only.

SQL (Structured Query Language) is the standard way to communicate with database servers in a Relational Database Management System (RDBMS). It was originated in 70s, as a domain specific language to conduct database queries. The name initially was SEQUEL (Structured English Query Language) later renamed to SQL due to copyright issues. The definition and intuitive capabilities of SQL made it popular and became integral part of RDBMS supplied by most vendors, PostgreSQL is no exception. The SQL was standardized in ANSI X3.135 in 1986 later adopted by ISO as ISO 9075-1987. The ISO committee revises it periodically and the most recent revision was in 2016 in nine parts (ISO/IEC 9075-1:2016, ISO/IEC 9075-2:2016, ISO/IEC 9075-3:2016, ISO/IEC 9075-4:2016, ISO/IEC 9075-9:2016, ISO/IEC 9075-10:2016, ISO/IEC 9075-11:2016, ISO/IEC 9075-14:2016, ISO/IEC 9075-19:2016). Donald Chamberlin and Ray Boyce are the well-known progenitors of this language.

SQL in PostgreSQL

PostgreSQL is an Object Relational Database System (ORDBMS) based upon POSTGRES version 4.2, a project of University of California, Berkeley, CS Department who pioneered many new concepts that was later adopted by many commercial database systems. PostgreSQL is open source and the current version (version 12) supports a large part of SQL:2016 core standard features (160 of 179) with a long list of optional features. This grossly means that standard SQL syntax can be used to communicate with the database provided by PostgreSQL system.

PostgreSQL uses client/server model communication; therefore, the server continually runs and waits for clients request to come in and responses back with appropriate result. Note that PostgreSQL runs as an independent process on the computer. Users cannot interact with it directly. A client interface has been designed specifically for user interaction. Similar to many modern database systems, there is a GUI interface called pgAdmin to make life easier for the developer to deal with administrative usages of PostgreSQL. The GUI also accommodates psql client application interface where SQL can be written.

Figure 1 – PgAdmin4 GUI

Otherwise, SQL shell (psql) can be used to write SQL commands as follows.

Figure 2 – SQL Shell (psql)

Getting Started with psql

At this point I assume that:

• PostgreSQL is installed properly
• The server is up and running
• It has been configured properly

Note: These are easy steps mostly accomplished during installation phases. If in doubt, do not forget to consult the manual. It has all the information to make PostgreSQL functional.

PostgreSQL controls several databases. From the point of view the server these databases are storage areas to store repository, such as, employee database, payroll database, inventory database and so on. A database is empty when first created, later tables are created, and relationships added among them using DDL (Data Definition Language). The DDL part of SQL is used to specify database schema structure while DML (Data Manipulation Language) part of the SQL is used to access, retrieve, insert and modify data to and from the database.

• Typical DDL commands of SQL: CREATE, ALTER, DROP, RENAME, TRUNCATE etc.
• Typical DML commands of SQL: SELECT, INSERT, UPDATE, DELETE, MERGE, CALL etc.

We start a psql session and connect to the database, in our case postgres. To keep things simple, I’ll use SQL Shell (psql) as the SQL interface to PostgreSQL server.

Hands on SQL in psql

Once you have connected the prompt should appear as shown in Fig 2. To test try some basic SQL commands (one may use lower or uppercase letters, does not matter unless you are typing string in quotes):

postgres=# SELECT CURRENT_USER;

 current_user 
--------------
 postgres
(1 row)

postgres=# SELECT CURRENT_TIMESTAMP;

        current_timestamp         
----------------------------------
 2019-11-22 12:20:37.242123+05:30
(1 row)

Multiline query:

postgres=# SELECT
postgres-# 10-8+6
postgres-# ;

Result

?column? 
----------
        8
(1 row)

Notice how the prompt changes from postgres=# to postgresql-# in multiline query. And a SQL command as always ends with a semicolon (;). The psql maintains a buffer that we can use it to our advantage while typing queries. For example, you can use backslash-p (\p) to see the content of the query buffer. You also can use left, right arrow keys to move around the text you have entered in the prompt and up and down arrow key to retrieve previously typed query. The buffer can be erased with backslash-r (\r). To quit psql type backslash-q (\q). Just type backslash-? (\?) to get more such commands.

CREATE database

There are two ways to create a database in PostgreSQL. The simplest one is as follows:

CREATE DATABASE employees;

There is a wrapper around this SQL command to create database called createdb. This command is located in the bin directory where PostgreSQl is installed. For example, you can create one in the terminal as follows.

createdb -h localhost -p 5432 -U postgres employees
password ****

This will prompt for password of the admin user.

You can then find out the list of databases available in the server by typing following in the SQL Shell (psql).

postgres-# \l

Note: To switch between databases from SQL Shell (psql) terminal, you can write:

postgres-# \c employees

This would change the current database from default postgres to a new database you may have created, say, employees. The prompt will change to employees as follows to indicate the change.

employees-#

CREATE table

The mathematical basis of a relational database is that all data stored in it are arranged in a uniform structure. This structure is made visible in the form of a table. Tables are the foundation of relational database management system (RDBMS). Each table has a name signifying the entity it represents, records of the entity denoted by rows and columns denotes the properties of the entity.

In PostgreSQL, we may loosely call a Table as a schema, relation or class, row as a record or tuple, column as field or attribute.

Let’s create one in psql.

CREATE TABLE employee
(
   emp_id INTEGER NOT NULL,
   birth_date DATE,
   email CHAR(20) ,    
   emp_name CHAR(30),
   join_date DATE,
   phone CHAR(10),
   CONSTRAINT employee_pkey PRIMARY KEY (emp_id)
);

This would create the employee table as follows.

The type associated with the column name indicates the column type and length. For example, CHAR (10) means the column holds a maximum of 10 characters. There are different column types supported by PostgreSQL such as DATE, INTEGER, BIGINT etc. You can find the information of a specific table as follows:

postgres=# \d employee

Insert data

You must use single quotes to insert character data, double quotes will not work. Numeric data is inserted without any quotes. And for date, you must also use single quotes. Let’s insert some data.

INSERT INTO employee VALUES (101,'10-10-1987','abc@gmail.com','Sameer Rawat','1-3-2015','9876543210');

When using INSERT command, you must make sure that each piece of data matches with the receiving column. Otherwise, you must write a more elaborately query as follows:

INSERT INTO employee(emp_id, emp_name,birth_date,email,join_date,phone) VALUES (102,’Arvind Verma’,’2-3-1988′,‘av@gmail.com‘,’2-3-2014′,’1234567890’);

Viewing data

Now, to make sure both the record has been rightly stored in the database you can enter the following command.

The only command to retrieve data from the database is SELECT. This command tells the database that we want to retrieve data. But what data? The asterisk (*) means all the data. But from where? The FROM clause tells that it is from the employee table. The clause ASC LIMIT 100 denotes that first hundred rows will be returned.

SELECT * FROM employee ASC LIMIT 100;

Understand that, SELECT has a large number of variations. Here you’ll see only a few.

SELECT COUNT(emp_id) FROM employee;SELECT emp_name,phone FROM employee;

SELECT emp_name, birth_date FROM employee WHERE emp_id < 103;

Removing data

Removing data from a database table is pretty simple. The DELETE command can remove any row in the table. Also, you can use the same command to remove all the rows from the table.

DELETE FROM employee WHERE emp_id = 101;

This will delete a single record whose emp_id is 101. Now, if you write DELETE command as follows it will delete all record the table in our case because you have set all emp_id greater than 100.

DELETE FROM employee WHERE emp_id > 100;

Modifying data

The UPDATE command can be used to modify existing data in the table. Suppose, you want to change the phone number of the employee record with emp_id 101, you can do so in the following manner.

UPDATE employee SET phone = ‘1122334455’ WHERE emp_id = 101;

The SET phone = ‘…’ sets the new value to the particular record selected by the WHERE clause. The record is modified accordingly.

Destroying table

Unlike anything, destroying a table is actually very simple. It is done with the help of DROP command.

DROP tablename;

This would efface completely the table from the database. That’s all.

Conclusion

Here we have scratched upon some of the key ideas of PostgreSQL database and how to interact with the server using trusty SQL commands. PostgreSQL is an excellent opensource ORDBMS package, alternative only to Oracle in its features. SQL being the primary interactive language, you have glimpsed how to use this in PostgreSQL in brief. Note that there is nothing new in the sense that you can do almost anything with SQL in PostgreSQL, as you may have already learnt. Stay tuned we’ll touch upon many interesting concepts in PostgreSQL. Happy learning.

# # #