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:
1 | apt show postgresql |
If the above command returns a valid package for Postgres, execute the following compound command to update the APT repository and install PostgreSQL:
1 | sudo apt update && sudo apt install postgresql postgresql-contrib -y |
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:
1 | brew doctor && brew update |
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:
1 | brew 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:
1 | service postgresql status |
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:
1 | sudo su - postgres |
Now start the interactive terminal for Postgres using the following psql command:
1 | psql |
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:
1 | CREATE DATABASE db; |
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:
1 | SELECT datname FROM pg_database; |
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:
1 | psql -U user_name -d db_name |
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:
1 | \c another_db |
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:
1 | \c db |
After switching to another database, use the following DROP DATABASE SQL statement to delete the desired database:
1 | DROP DATABASE another_db; |
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:
1 2 | CREATE TABLE TABLE_NAME (COLUMN_NAME + DATATYPE + CONSTRAINTS [OPTIONAL]); |
Here is an example of how to create a Postgres table called sample:
1 2 3 4 5 | CREATE TABLE sample( id SERIAL PRIMARY KEY, name VARCHAR(50), address TEXT ); |
The results should resemble the following:
1 2 | db=# CREATE TABLE sample(id SERIAL PRIMARY KEY, name VARCHAR(50), address TEXT); CREATE TABLE |
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started