PostgreSQL psql client tool commands equivalent to Oracle SQL*Plus client tool
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.
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.
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.
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.
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:
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.
For host, port, and user, we have the following connection options:
- -h, –host –
HOSTNAMEis the database server host or socket directory (the default is local socket)
- -p, –port –
PORTis the database server port (the default is 5432)
- -U, –username –
USERNAMEis the database user name (the default is
- –d, –database –
DBNAMEis the database name (the default is same as
- -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
\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.
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.
|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.||
|Toggles the display of output column name headings and row count footer.||
|Turns displaying of how long each SQL statement takes.||
|Toggles pager use on and off.||
|Specifies the record (line) separator to use in unaligned output format.||
|Toggles pager use off.||
|Switches to HTML output mode.||
|Switches to CSV output mode.||
|Sets delimiter for CSV output mode.||
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.
|Runs the current query buffer||
|Prints the current query buffer||
|Runs the SQL file||
|SQL output spool||
|Controls whether or not to echo commands in a script||
|Sets the text that represents a null value in the result of a SQL SELECT command||
Information display commands
The following table contains the commands that display information about the database objects and SQL*Plus or psql itself.
|Gets details of objects like table, view, and procedural object (packages, procedure)||
|Gets help of the commands||
|The external editor command||
The following table contains the commands to get the general, systematic functions of SQL*Plus and psql.
|Connects a given user to the database||
|Exits the session||
|Defines or sets a variable||
|Controls the COMMIT operation||
|Changes the connected database user 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
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
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:
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 can be used to get more information of the objects like tables, views, sequences, indexes, functions, etc.
\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.
\d commands are available in psql documentation.
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:
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:
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.
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.