PostgreSQL psql client tool commands equivalent to Oracle SQL*Plus client tool

December 2023: This post was reviewed and updated for accuracy.

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, the most common challenges that any developer or DBA face during the usage of PostgreSQL are 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 is installed with every Oracle database installation as well as SQL*Plus 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 perform the following tasks:

• 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 the Oracle Database client tools for different operating systems, versions, and features. For our SQL*Plus use case only, the Oracle Instant Client is a good choice.

When installing Oracle Instant Client, you 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:

export TNS_ADMIN=/some/path/to/tnsnames.ora 
sqlplus dbuser/dbpassword@db_tns_name

Note that db_tns_name is a TNS alias for a valid connection string configured in the tnsnames.ora file.

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 commands set a system variable to alter the SQL*Plus environment settings for your current session, and they are used to put into shape in terms of line size, page size, and additional useful configurations. You can display all the SET commands by running the help set command as follows:

SQL> help set

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, you can use the following commands to download the psql command line tool:

sudo yum update
sudo amazon-linux-extras enable postgresql14 > /dev/null
sudo yum install postgresql

[ec2-user@ip-172-31-87-37 ~]$ psql --version
psql (PostgreSQL) 14.8

Connection strings

With psql, you have multiple ways to specify connection options, such as key-values, command text, or as per connection URI.

You can connect to the database using psql client with the following command, and by default, you will need to provide the password.

psql -h <<hostname>> -U <<user>> -d <<dbname>> -p <<port>>
Password for user postgres:

But, instead of typing the password every time, you can use the PGPASSWORD environment to hold the password and psql will not prompt for password like shown in the following example:

export PGPASSWORD=xxxxx
psql -h <<hostname>> -U <<user>> -d <<dbname>> -p <<port>>

However, it is recommended to use a .pgpass hidden file, to improve the security. It resides in the operating system user’s home directory and can be protected with restricted permissions at filesystem level.

psql -h <<hostname>> -U <<user>> -d <<dbname>> -p <<port>>

Key-values

For host, port, and user, you 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 while \set is run on the client.

Comparison of SQL*Plus and psql meta commands

This section compares the meta commands of SQL*Plus and psql, which can be categorized as follows:

• Format commands
• Input and output commands
• Information display commands
• System commands

Note that only a small subset of psql features have been compared with sqlplus client, the most popular ones. However, you can see more features in the “psql specific features” subsequent section.

Format commands

The following table contains the commands that help you format the SQL query output.

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.

Information display commands

The following table contains the commands that display information about the database objects and SQL*Plus or psql itself.

System commands

The following table contains the commands to get the general, systematic functions of SQL*Plus and psql.

Convert meta commands using the AWS Schema Conversion Tool

The AWS Schema Conversion Tool (AWS SCT) helps you convert an Oracle-based SQL script that consists of some of SQL*Plus features to PostgreSQL and psql compatible syntax.

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.

Note that with AWS SCT you have the possibility to define AUTOCOMMIT configuration while converting SQL scripts from Oracle SQL*Plus to PSQL: in PSQL the autocommit is “on” by default, this is a best practice to avoid “idle in transaction” sessions, while this is not true for SQL*Plus. You may decide to keep the default for PSQL or switch the autocommit off, to preserve the SQL*Plus behavior.

PSQL specific features

The following are some psql specific features that cannot be directly compared to sqlplus, some of them can be reproduced combining different tools.

\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:

3c22fb76c717:~$ cat /tmp/a.out
1,test1
2,test2
3,test2
3c22fb76c717:~ $ psql -h test-instance-xxxxx.us-east-1.rds.amazonaws.com -p 5432 -U postgres -d postgres
Type "help" for help.

postgres=> create table psql_copy(id int, name varchar);
CREATE TABLE
postgres=> \copy psql_copy from '/tmp/a.out' delimiter ',';
COPY 3
postgres=> select * from psql_copy ;
 id | name
----+-------
  1 | test1
  2 | test2
  3 | test2
(3 rows)

postgres=>

Performing a similar task with Oracle requires you to use multiple tools:

1. Database table to file (set sqlformat csv)
2. File to Database table : sqlloader / external table
3. Database table to other Database table using COPY command

\x – extended output

Often, 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:

postgres=> select * from pg_stat_activity where pid = pg_backend_pid();
 datid | datname  | pid  | usesysid | usename  | application_name |  client_addr  | client_hostname | client_port |         backend_start         |          xact_start           |          query_start          |         state_change          | wait_event_type | wait_event | state  | backend_xid | backend_xmin |                            query                             |  backend_type
-------+----------+------+----------+----------+------------------+---------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------------+------------+--------+-------------+--------------+--------------------------------------------------------------+----------------
 13590 | postgres | 3519 |    16399 | postgres | psql             | xxxxx |                 |        5750 | 2022-03-20 18:30:45.055329+00 | 2022-03-20 18:38:56.284395+00 | 2022-03-20 18:38:56.284395+00 | 2022-03-20 18:38:56.284396+00 |                 |            | active |             |     54726780 | select * from pg_stat_activity where pid = pg_backend_pid(); | client backend
(1 row)

postgres=>
postgres=> \x
Expanded display is on.
postgres=> select * from pg_stat_activity where pid = pg_backend_pid();
-[ RECORD 1 ]----+-------------------------------------------------------------
datid            | 13590
datname          | postgres
pid              | 3519
usesysid         | 16399
usename          | postgres
application_name | psql
client_addr      | xxxxx
client_hostname  |
client_port      | 5750
backend_start    | 2022-03-20 18:30:45.055329+00
xact_start       | 2022-03-20 18:39:02.090877+00
query_start      | 2022-03-20 18:39:02.090877+00
state_change     | 2022-03-20 18:39:02.090879+00
wait_event_type  |
wait_event       |
state            | active
backend_xid      |
backend_xmin     | 54726789
query            | select * from pg_stat_activity where pid = pg_backend_pid();
backend_type     | client backend

postgres=>

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:

postgres=> select 'this is a psql' as var1, 'feature' as var2
postgres-> \gset
postgres=> \echo :var1 :var2
this is a psql feature
postgres=>
postgres=> select 'this is a psql' as var1, 'feature' as var2
postgres-> \gset myvars_
postgres=> \echo :myvars_var1 :myvars_var2
this is a psql feature
postgres=>

\s – query history

You can use \s to get the list of commands in the history. The following is an example:

postgres=> \s
create table psql_copy(id int, name varchar);
\copy psql_copy from '/tmp/a.out';
\copy psql_copy from '/tmp/a.out' delimiter ',';
select * from psql_copy ;
select * from pg_stat_activity where pid = pg_backend_pid();
\x
select * from pg_stat_activity where pid = pg_backend_pid();
\q
select 'this is a psql' as var1, 'feature' as var2
\gset
\echo :var1 :var2
select 'this is a psql' as var1, 'feature' as var2
\gset myvars_
\echo :myvars_var1 :myvars_var2

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:

postgres=> create table tab1(id int);
CREATE TABLE
postgres=> create table tab2(id int);
CREATE TABLE
postgres=>
postgres=> select * from tab
tab1 tab2
postgres=>

\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.

                  Table "public.psql_copy"
 Column |       Type        | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
 id     | integer           |           |          |
 name   | character varying |           |          |

postgres=> \d+ psql_copy
                                        Table "public.psql_copy"
 Column |       Type        | Collation | Nullable | Default | Storage  | Stats target |  Description
--------+-------------------+-----------+----------+---------+----------+--------------+----------------
 id     | integer           |           |          |         | plain    |              | my id column
 name   | character varying |           |          |         | extended |              | my name column
Access method: heap

postgres=> create view v_psql_copy as select * from psql_copy ;
CREATE VIEW
postgres=>
postgres=> \d+ v_psql_copy
                              View "public.v_psql_copy"
 Column |       Type        | Collation | Nullable | Default | Storage  | Description
--------+-------------------+-----------+----------+---------+----------+-------------
 id     | integer           |           |          |         | plain    |
 name   | character varying |           |          |         | extended |
View definition:
 SELECT psql_copy.id,
    psql_copy.name
   FROM psql_copy;

postgres=>
postgres=> \dt+ psql_copy
                             List of relations
 Schema |   Name    | Type  |  Owner   | Persistence | Size  | Description
--------+-----------+-------+----------+-------------+-------+--------------
 public | psql_copy | table | postgres | permanent   | 16 kB | 
(1 row)

postgres=>

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:

postgres=> select 'vacuum verbose analyze '||relname||';' from pg_class where relname like 'join%';
           ?column?
-------------------------------
 vacuum verbose analyze join1;
 vacuum verbose analyze join2;
(2 rows)

postgres=> select 'vacuum verbose analyze '||relname||';' from pg_class where relname like 'join%'
postgres-> \gexec
INFO:  vacuuming "public.join1"
INFO:  "join1": found 0 removable, 5 nonremovable row versions in 1 out of 1 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 54729530
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  analyzing "public.join1"
INFO:  "join1": scanned 1 of 1 pages, containing 5 live rows and 0 dead rows; 5 rows in sample, 5 estimated total rows
VACUUM
INFO:  vacuuming "public.join2"
INFO:  "join2": found 0 removable, 5 nonremovable row versions in 1 out of 1 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 54729531
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  analyzing "public.join2"
INFO:  "join2": scanned 1 of 1 pages, containing 5 live rows and 0 dead rows; 5 rows in sample, 5 estimated total rows
VACUUM
postgres=>

if-else

The if-else statement available in psql client is useful to run conditional block in client side.

\watch [ seconds ]

This is a very useful command when you want to monitor some events over time. It executes 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 every 5 secs:

postgres=> select now()-query_start, datname, usename, query from pg_stat_activity where query like '%sleep%' and pid<>pg_backend_pid() and state='active';
    ?column?     | datname  | usename  |        query
-----------------+----------+----------+----------------------
 00:00:02.802127 | postgres | postgres | select pg_sleep(10);
(1 row)

postgres=> \watch 5
             Mon Mar 21 00:53:23 2022 (every 5s)

    ?column?    | datname  | usename  |        query
----------------+----------+----------+----------------------
 00:00:04.02338 | postgres | postgres | select pg_sleep(10);
(1 row)

             Mon Mar 21 00:53:28 2022 (every 5s)

    ?column?     | datname  | usename  |        query
-----------------+----------+----------+----------------------
 00:00:09.252449 | postgres | postgres | select pg_sleep(10);
(1 row)

 Mon Mar 21 00:53:33 2022 (every 5s)

 ?column? | datname | usename | query
----------+---------+---------+-------
(0 rows)

~/.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 they login to the database. In PSQL, this file is named as .psqlrc and it resides 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:

3c22fb76c717:~$ cat ~/.psqlrc
\set QUIET 1
\pset null '[null]'
\x auto
\timing
\echo 'Welcome to PostgreSQL! \n'
\echo 'Type :version to see the PostgreSQL version. \n'
\echo 'Type \\q to exit. \n'
\set version 'SELECT version();'
3c22fb76c717:~ $
3c22fb76c717:~ $
3c22fb76c717:~ $ psql -h test-instance.xxxx.us-east-1.rds.amazonaws.com -p 5432 -U postgres -d postgres
Welcome to PostgreSQL!

Type :version to see the PostgreSQL version.

Type \q to exit.

postgres=> select 1;
 ?column?
----------
        1
(1 row)

Time: 221.898 ms
postgres=>

\? – HELP

You can use \? to get help on all the available commands.

postgres=> \?
General
  \copyright             show PostgreSQL usage and distribution terms
  \crosstabview [COLUMNS] execute query and display results in crosstab
  \errverbose            show most recent error message at maximum verbosity
  \g [(OPTIONS)] [FILE]  execute query (and send results to file or |pipe);
                         \g with no arguments is equivalent to a semicolon
  \gdesc                 describe result of query, without executing it
  \gexec                 execute query, then execute each value in its result
  \gset [PREFIX]         execute query and store results in psql variables
  \gx [(OPTIONS)] [FILE] as \g, but forces expanded output mode
  \q                     quit psql
  \watch [SEC]           execute query every SEC seconds
.
.
.

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 better understand psql. You can install the SQL*Plus and PSQL clients by referring the installation sections and try the commands, and you can also automatically convert SQL*Plus scripts to PSQL syntax with AWS Schema Conversion Tool.

If you have any questions or suggestions about this post, feel free to leave a comment.

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.

Domenico di Salvia is a Senior Database Specialist Solutions Architect at AWS. In his role, Domenico works with customers in EMEA region to provide guidance and technical assistance on database projects, helping them improve the value of their solutions when using or migrating to AWS, designing scalable, secure, performant, sustainable, cost-effective and robust database architectures in the AWS Cloud.

Audit History

Last reviewed and updated in December 2023 by Domenico di Salvia | Sr. RDS PostgreSQL Solutions Architect