PostgreSQL Psql Examples (Part 1)

Introduction

This is the first part of a tutorial series providing PSQL examplesx000D. The tutorial will provide SQL statement and query examples for the PSQL command line interface for Postgres. Psql, or PostgreSQL interactive terminal, is a front-end to PostgreSQL that enables DBAs to make interactive queries, issue the queries to PostgreSQL and view the query results. Inputs can also be placed from a file or command line argument. Psql also provides for various meta-commands and shell-like features to assist with a broad range of tasks.

Prerequisites

• PostgreSQL must be properly installed and working with access to the psql command line interface.

• Possess a rudimentary knowledge of database management systems operation and SQL commands for the psql PostgreSQL interface.

Install Postgres and psql

On Debian-based distros of Linux, such as Ubuntu or Linux Mint, Postgres should be able to be installed using the APT repository.

Execute the following command to verify there is an existing installation package for the Debian version of Linux being used:

If the above command returns a valid package for Postgres, execute the following compound command to update the APT repository and install PostgreSQL:

NOTE: The -y flag will automatically input yes if the installation request a password.

Install Postgres on macOS using Homebrew

If using macOS, install PostgreSQL using Homebrew’s brew command. Execute the following command to conduct a health check on the Homebrew installation and update its package repositories:

NOTE: Depending on the macOS version, it may be necessary to check that the Xcode is also up-to-date.

Once those functions are completed, use the following brew install command to install PostgreSQL:

If running PostgreSQL on Windows systems, go here to download and execute an x86 interactive installer that matches either the 32-bit or 64-bit version of Windows.

Get the psql version number

Execute the psql -V command to display the currently loaded PostgreSQL version. Refer to the screenshot at the end of this section.

If using a Linux machine, the following command can also be used to verify if the PostgreSQL server is running:

NOTE: If using the Homebrew installation of Postgres for macOS, then use launchctl plists or brew services start postgresql to start PostgreSQL automatically when the Mac boots.

To get information on Homebrew Postgres server use brew info postgres.

Press CTRL+C (or CTRL+SHIFT+C) to cancel the process and return to the terminal prompt.

psql examples

Access the psql interface to create databases and tables to use for going over a few examples of how to use the interface.

Access the psql command line

First, the command required to grant access to the psql command-line console must be input to connect to the psql command-line console. The system will grant login privilege to the postgres super user after entering the password and the following command:

Now start the interactive terminal for Postgres using the following psql command:

The command line interface will now be able to edit and execute queries in PostgreSQL.

psql command line examples

Below are a few psql examples of SQL commands and statements that can be used to query and modify a PostgreSQL database and table data:

psql create a database example

The following command is an example of how to create a database in PostgreSQL:

NOTE: The lowercase db is just an example used for the database name.

Postgres list databases

All of the databases for PostgreSQL can be displayed using the \l command, or the following SQL SELECT keyword can be used:

psql connect to a database example

As shown in the following example, a database can be accessed while connecting to psql using the -d flag with psql command:

Alternatively, if a connection to a database in psql has already been established, but switching to another database is required, execute the following \c psql command:

The \conninfo command can also be used to obtain more information about the connection to the Postgres database.

psql drop database example

The \c connect command can be used for deleting a Postgres databases, as a database with an active connection cannot be dropped. Establish a connection back to the first database with the following command:

After switching to another database, use the following DROP DATABASE SQL statement to delete the desired database:

The results should resemble the following:

psql ‘CREATE TABLE’ example

Following is an example of the syntax of command on how to create a table in PostgreSQL:

Here is an example of how to create a Postgres table called sample:

The results should resemble the following:

The CREATE TABLE clause placed before the table_name lists all of the columns and data types. A table may have multiple columns separated by a comma (,). Constraints may be placed on the columns, along with the data types, to restrict what can and what cannot be inserted into a particular column.

Conclusion

This was part one of a tutorial series providing several PSQL examplesx000D on how to query and modify a PostgreSQL database, table and record data. Part one specifically explained how to use the commands for psql examples in PostgreSQL. This tutorial covered how to install Postgres and psql on Windows, Linux and Mac and obtain the current psql version number. The article provided psql examples, explained how to access the psql command line and create a psql database example. Part two of this series will go into further detail on commands for psql examplesx000D in PostgreSQL.