When you’re working as an application developer or database administrator, you often need to deal with SQL statements to explore your database for various reasons. You need to connect to the database to build or run the SQL code, generate reports, and diagnose application problem related to the database.
There are various ways to connect to a database. You can directly log in to the database server host and use a utility to manage the database, or you can use a desktop GUI or CLI tools or web browser-based interface to interact with the database server remotely.
In general, when you connect to a database server using any database command line client tool, you run two types of commands on the CLI:
Commands processed by the database CLI client locally, often known as meta commands. These commands help to perform the following:
Perform calculations on, store, and print from query results
Set the display width for data
Customize HTML formatting
Enable or disable printing of column headings
Set the number of lines per page
Examine table and object definitions
Commands processed by database server, often known as server commands or database commands.
PostgreSQL has become the preferred open-source relational database for many enterprise developers and startups, and powers leading business and mobile applications. PostgreSQL is considered to be the primary open-source database choice when migrating from commercial databases such as Oracle. AWS offers services that make PostgreSQL database deployments easy to set up, manage, and scale for the cloud in a cost-efficient manner. These services are Amazon Relational Database Service (Amazon RDS) for PostgreSQL and Amazon Aurora with PostgreSQL compatibility. After you migrated your databases from Oracle to PostgreSQL, most common challenges that any developer or DBA face during the usage of PostgreSQL is with PSQL client as they are used to Oracle SQL*Plus client tool.
In this post, we discuss SQL*Plus (mostly applicable for 12c and 19c version), which is an Oracle command line client tool, and psql (higher than 9.6 Version), which is a PostgreSQL command line client tool. We also provide Oracle SQL*Plus equivalent commands in PostgreSQL psql.
The following diagram illustrates the process of the meta and database commands of SQL*Plus and PSQL client. The meta commands are processed by the client tool itself, and database commands are processed by the database server.
Oracle SQL*Plus
SQL*Plus is an interactive and batch query tool that’s installed with every Oracle database installation as well as SQL*Plus Instant Client. It has a command line user interface and its own commands and environment, and it provides access to the Oracle database. It enables you to do the following:
Connect to an Oracle database
Enter and run SQL commands and PL/SQL blocks
Format and print query results using meta commands
Run administrative commands like database startup and shutdown
Run OS commands within the SQL*Plus CLI prompt
Enter SQL*Plus commands to configure the SQL*Plus environment
You can use SQL*Plus to generate reports interactively and output the results to a text file or on screen, or to an HTML file for browsing on the internet. You can generate reports dynamically using the HTML output facility of SQL*Plus.
SQL*Plus installation
There are various ways you can install Oracle Database client tools for different operating systems, versions, and features. For our SQL*Plus use case only, Oracle Instant Client is a good choice.
When installing Oracle Instance Client, we need the Basic and SQL*Plus package to establish a SQL*Plus session with Oracle Database.
After you install the Oracle Instant Client base on your OS type, you need to set some environment variables. For more information, refer to Install Oracle Database Instant Client.
After a successful installation, you may connect to the database using the following command:
You can also use various options (like Silent login, compatibility, and markup) during connection initialization. For a full list, refer to Options.
SQL*Plus meta commands
SQL*Plus meta or SET commands are different than the normal SQL standard commands used to query the data. These SET commands set a system variable to alter the SQL*Plus environment settings for your current session. These are used to put into shape in terms of line size, page size, and so on. You can display all the SET commands by running the help set command as follows:
You can enter a hyphen at the end of the line to continue a long SQL*Plus command and press Enter. If you want, you can type a space before typing the hyphen. SQL*Plus displays a right angle-bracket (>) as a prompt for each additional line.
You don’t need to end a SQL*Plus command with a semicolon. When you finish entering the command, you can press Enter. If you want, however, you can enter a semicolon at the end of a SQL*Plus command.
For more information about SET system variables, SET System Variable Summary.
PostgreSQL psql
The primary front end for PostgreSQL is the psql command line client, which you can use to enter SQL queries directly or run them from a file. In addition, psql provides several meta commands and various shell-like features to facilitate writing scripts and automating a wide variety of tasks; for example, tab completion of object names and SQL syntax.
Installation
You can download the psql command line for different operating systems using the PostgreSQL download libraries.
On Amazon Linux 2, we can use the following commands to download the psql command line tool:
Connection strings
With psql, we have multiple ways to specify connection options, such as key-values, command text, or as per connection URI.
You can connect the database using psql client using the following command. By default, you will need to provide the password.
For password as credentials, you can use the PGPASSWORD environment to hold the password. It will not prompt for password.
However, it is recommended to use .pgpass file. It resides in operating system user’s home directory.
Key-values
For host, port, and user, we have the following connection options:
-h, –host – HOSTNAME is the database server host or socket directory (the default is local socket)
-p, –port – PORT is the database server port (the default is 5432)
-U, –username – USERNAME is the database user name (the default is OS User)
–d, –database – DBNAME is the database name (the default is same as USERNAME)
-w, –no-password – Never prompt for password
–W, –password – Force a password prompt (should happen automatically)
For more information, see Connection Strings.
PSQL meta commands
Anything that you enter in psql that begins with an unquoted backslash is a psql meta command that is processed by psql itself. These commands make psql more useful for administration or scripting. Meta commands are often called slash or backslash commands.
In general, two types of meta commands are used in psql:
set – Sets the psql variable name to value […]
pset – Sets options affecting the output of query result tables
Note that set and set commands are different in psql. SET is run on the database server and set is on the client
Comparison of SQL*Plus and psql meta commands
This section compares the meta commands of SQL*Plus and psql, which we have categorized as follows:
Format commands
Input and output commands
Information display commands
System commands
Note that we have used a small subset of psql features to compare with sqlplus client. However, you can see more features in “psql specific features” section.
Format commands
The following table contains the commands that help you format the SQL query output. You can set the format of the results the way you want.
Description
SQL*Plus
PSQL
The comment at the end of your listing that tells you how many rows were returned. If you turn feedback off, and your query returns no rows, you simply return to the SQL prompt.
set feedback off
set QUIET on
Toggles the display of output column name headings and row count footer.
set heading off
t
Turns displaying of how long each SQL statement takes.
set timing on
timing
Toggles pager use on and off.
SET PAUSE (ON|OFF)
pset pager
Specifies the record (line) separator to use in unaligned output format.
set recsep off
pset recordsep
Toggles pager use off.
SET PAGESIZE 0
pset pager off
Switches to HTML output mode.
SET MARKUP HTML ON
H or html or pset format html
Switches to CSV output mode.
SET MARKUP CSV ON
pset format csv
Sets delimiter for CSV output mode.
SET MARKUP CSV ON DELIMITER <<character>>
pset csv_fieldsep <<character>>
Input and output commands
The following table contains the input and output commands, which allow you to transfer the data to and from the database in different ways.
Description
SQL*Plus
PSQL
Runs the current query buffer
g*
Prints the current query buffer
l
p or print
Runs the SQL file
@file_name
i file_name.sql or ir
SQL output spool
spool output_file_name
o
Substitution variable
&var_name
accept var_name
prompt ‘Your Text’ var_name
Controls whether or not to echo commands in a script
SET ECHO {ON | OFF}
set ECHO all
Sets the text that represents a null value in the result of a SQL SELECT command
SET NULL text
pset NULL text
Information display commands
The following table contains the commands that display information about the database objects and SQL*Plus or psql itself.
Description
SQL*Plus
PSQL
Gets details of objects like table, view, and procedural object (packages, procedure)
DESCRIBE
d and df
Gets help of the commands
HELP command
h command
The external editor command
EDIT or ED
e
System commands
The following table contains the commands to get the general, systematic functions of SQL*Plus and psql.
Description
SQL*Plus
PSQL
Connects a given user to the database
CONNECT
c or connect
Exits the session
exit
q or exit
Defines or sets a variable
DEFINE name=value
set name value
Controls the COMMIT operation
SET AUTO[COMMIT]{ON | OFF | IMM[EDIATE] | n}
set AUTOCOMMIT off
Changes the connected database user password
password
password
Convert meta commands using the AWS Schema Conversion Tool
The AWS Schema Conversion Tool (AWS SCT) helps us convert an Oracle-based SQL script that consists of some of SQL*Plus features to PostgreSQL compatible as per the psql command line.
For more information about converting meta commands, refer to Analyzing and converting your SQL code using the AWS SCT.
The following screenshot is an example of a SQL*Plus command supported as part of conversion to PostgreSQL as psql command line compatible.
PSQL specific features
The following are some psql specific features when compared to sqlplus.
copy
This runs an SQL COPY command (server command), but instead of the server reading or writing the specified file, psql reads or writes the file and routes the data between the server and the local file system using the privileges of the local user. This allows a user to bulk load data into the database without requiring superuser privileges.
The following is an example of using copy:
In Oracle, we need to use different tool for this
Database table to file (set sqlformat csv)
File to Database table : sqlloader / external table
Database table to other Database table using COPY command
x – extended output
It is difficult to read the output of a query with a lot of columns in fetch list. The x sets extended formatting for the query results as shown in the following output:
UP and DOWN keys
You can use up and down arrows to scroll through the command history. Up and down arrow keys to move backwards and forwards through the history of commands.
gset [ prefix ]
Sends the current query buffer to the server and stores the query’s output into psql variables. The following is an example:
s – query history
You can use s to get the list of commands in the history. The following is an example:
Tab
You can hit tab to complete the keywords while writing the commands in psql client. For example, if you type “SEL” and hit tab, it will complete the keyword as “SELECT”. It also helps to complete the table names. If you have tables that starts with TAB, you can type the command to write the query and hit tab twice to see the table names start with TAB. The following is an example:
d commands
d can be used to get more information of the objects like tables, views, sequences, indexes, functions, etc.
For example, d can be used to get column information of table, d+ get some more information about the table like comments on the columns or definition of the objects like view or function, and dt+ can be used to get the size of the table.
More d commands are available in psql documentation.
gexec
Sends the current query buffer to the server, then treats each column of each row of the query’s output as a SQL statement to be executed. The following is an example:
if-else
The if-else statement available in psql client is useful to run conditional block in client side.
watch [ seconds ]
This is very useful command when you want to monitor some events over time. It execute the buffered SQL in regular configurable interval ( default 2 sec) until interrupted. The following example shows the monitoring of pg_stat_activity for active sessions for every 5 secs:
~/.psqlrc
When working in CLI mode, developers often want to save their preferences/client’s customized behavior in a runtime configuration file and want it to get executed every time user login to the database. For PSQL, this file is named as .psqlrc and is reside in the invoking user’s home directory. This file mostly includes setting up or enabling of timing, formatting the output and more. The following is an example:
? – HELP
You can use ? to get help on all the available commands.
Summary
Database developers and administrators often use meta commands to get their desired information when working with the SQL*Plus command line tool. However, after you migrate from Oracle to PostgreSQL, you use the psql client tool to interact with the PostgreSQL database. This post discussed the basic functionalities of the SQL*Plus and psql tools and their meta commands, and compared the meta commands between these two tools to help you understand psql better. You can install the SQL*Plus and PSQL clients by referring the installation sections and try the commands.
If you have any questions or suggestions about this post, feel free to leave a comment. We hope the information we shared helps!
About the Authors
Sudip Acharya is a Sr. Consultant with the AWS ProServe team in India. He works with internal and external Amazon customers to provide guidance and technical assistance on database projects, helping them improve the value of their solutions when using AWS.
Deepak Mahto was a Consultant with the AWS Proserve Team in India. He worked as Database Migration Lead, helping and enabling customers to migrate from commercial engines to Amazon RDS. His passion is automation and has designed and implemented multiple database or migration related tools.
Baji Shaik is a Sr Lead Consultant with AWS ProServe, GCC India. His background spans a wide depth and breadth of expertise and experience in SQL/NoSQL database technologies. He is a Database Migration Expert and has developed many successful database solutions addressing challenging business requirements for moving databases from on-premises to Amazon RDS and Aurora PostgreSQL/MySQL. He is an eminent author, having written several books on PostgreSQL. A few of his recent works include “PostgreSQL Configuration“, “Beginning PostgreSQL on the Cloud”, and “PostgreSQL Development Essentials“. Furthermore, he has delivered several conference and workshop sessions.
Read MoreAWS Database Blog
