Saturday, May 28, 2022
No menu items!
HomeDatabase ManagementPostgreSQL psql client tool commands equivalent to Oracle SQL*Plus client tool

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.

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:

export TNS_ADMIN=/some/path/to/tnsnames.ora
sqlplus dbuser/[email protected]_tns_name

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:

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

sudo yum upgrade
sudo amazon-linux-extras enable postgresql13 > /dev/null
sudo yum install postgresql

[[email protected] ~]$ psql –version
psql (PostgreSQL) 13.3

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.

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

For password as credentials, you can use the PGPASSWORD environment to hold the password. It will not prompt for password.

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

However, it is recommended to use .pgpass file. It resides in operating system user’s home directory.

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

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:

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=>

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:

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

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

3c22fb76c717:~$ cat ~/.psqlrc
set QUIET 1
pset null ‘[null]’
x auto
timing
echo ‘Welcome to PostgreSQL! n’
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!

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

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments