Working with PostgreSQL through psql becomes far more powerful when you understand how its input and output options behave. These flags are not just minor command-line additions; they directly influence how queries are displayed, executed, logged, and debugged. For developers who run SQL scripts frequently or explore PostgreSQL internals, mastering these options provides better visibility and control over database interactions.
Base SQL Script Used for Testing
All examples in this guide use the following test.sql file:
Create a file named test.sql file
sudo nano test.sql
Copy these queries into this file.
CREATE TABLE IF NOT EXISTS demo (
id INT,
name TEXT
);
insert into demo(id,name)values (1,'jhon'),(2,'marc demo');
select * from demo;
insert into demo(id,name)values (3,'joy'),(4,'roy');
Running the Script Normally
psql -f test.sql -d postgres -U postgres
Observation
- The table is created only once.
- Data is inserted every time the script runs.
- The number of rows increases with each execution.
Example output progression:
psql:test.sql:4: NOTICE: relation "demo" already exists, skipping
psql:test.sql:4: NOTICE: DDL command executed
CREATE TABLE
INSERT 0 2
id | name
----+-----------
1 | jhon
2 | marc demo
3 | joy
4 | roy
1 | jhon
2 | marc demo
(6 rows)
INSERT 0 2
This occurs because the script repeatedly inserts the same data without clearing previous entries.
Option 1: Echo All Input (-a)
psql -f test.sql -d postgres -U postgres -a
Output Behavior
- Prints every line from the script before execution.
- Includes SQL statements exactly as written.
Example:
CREATE TABLE IF NOT EXISTS demo (
id INT,
name TEXT
);
psql:test.sql:4: NOTICE: relation "demo" already exists, skipping
psql:test.sql:4: NOTICE: DDL command executed
CREATE TABLE
insert into demo(id,name)values (1,'jhon'),(2,'marc demo');
INSERT 0 2
select * from demo;
id | name
----+-----------
1 | jhon
2 | marc demo
3 | joy
4 | roy
1 | jhon
2 | marc demo
3 | joy
4 | roy
1 | jhon
2 | marc demo
(10 rows)
insert into demo(id,name)values (3,'joy'),(4,'roy');
INSERT 0 2
Use Case
Useful when you want to verify exactly what the script contains and how it is being processed.
Option 2: Echo Errors (-b)
psql -f test.sql -d postgres -U postgres -b
Output Behavior
Displays errors and failed statements.
Shows successful query output also.
Example:
psql:test.sql:4: NOTICE: relation "demo" already exists, skipping
psql:test.sql:4: NOTICE: DDL command executed
CREATE TABLE
INSERT 0 2
id | name
----+-----------
1 | jhon
2 | marc demo
3 | joy
4 | roy
1 | jhon
2 | marc demo
3 | joy
4 | roy
1 | jhon
2 | marc demo
3 | joy
4 | roy
1 | jhon
2 | marc demo
(14 rows)
INSERT 0 2
Use Case
Helps isolate problems when running long or complex scripts.
Option 3: Echo Queries (-e)
psql -f test.sql -d postgres -U postgres -e
Output Behavior
Displays queries that are sent to the server.
Focuses on executed commands.
Example:
CREATE TABLE IF NOT EXISTS demo (
id INT,
name TEXT
);
psql:test.sql:4: NOTICE: relation "demo" already exists, skipping
psql:test.sql:4: NOTICE: DDL command executed
CREATE TABLE
insert into demo(id,name)values (1,'jhon'),(2,'marc demo');
INSERT 0 2
select * from demo;
id | name
----+-----------
1 | jhon
2 | marc demo
3 | joy
4 | roy
1 | jhon
2 | marc demo
3 | joy
4 | roy
1 | jhon
2 | marc demo
3 | joy
4 | roy
1 | jhon
2 | marc demo
3 | joy
4 | roy
1 | jhon
2 | marc demo
(18 rows)
insert into demo(id,name)values (3,'joy'),(4,'roy');
INSERT 0 2
Use Case
Useful for understanding the exact SQL being executed.
Option 4: Echo Hidden Queries (-E)
psql -d postgres -U postgres -E
Inside psql:
\d demo
Output Behavior
Displays internal queries generated by psql:
/******** QUERY *********/
/******** QUERY *********/
/******** QUERY *********/
SELECT c.oid::pg_catalog.regclass
FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
WHERE c.oid = i.inhparent AND i.inhrelid = '477459'
AND c.relkind != 'p' AND c.relkind != 'I'
ORDER BY inhseqno;
/************************/
/******** QUERY *********/
SELECT c.oid::pg_catalog.regclass, c.relkind, inhdetachpending, pg_catalog.pg_get_expr(c.relpartbound, c.oid)
FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
WHERE c.oid = i.inhrelid AND i.inhparent = '477459'
ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT', c.oid::pg_catalog.regclass::pg_catalog.text;
/************************/
Table "public.demo"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
name | text | | |
Use Case
Helpful for learning how PostgreSQL retrieves metadata from system catalogs.
Option 5: Log Session to File (-L)
psql -f test.sql -d postgres -U postgres -L session.log
If you are getting the permission denied issue,fix that issue by using the below chmod command
chmod 777 session.log
Output in Log File
/******** QUERY *********/
CREATE TABLE IF NOT EXISTS demo (
id INT,
name TEXT
);
/************************/
CREATE TABLE
/******** QUERY *********/
insert into demo(id,name)values (1,'jhon'),(2,'marc demo');
/************************/
INSERT 0 2
/******** QUERY *********/
select * from demo;
/************************/
id | name
----+-----------
1 | jhon
2 | marc demo
(2 rows)
/******** QUERY *********/
insert into demo(id,name)values (3,'joy'),(4,'roy');
/************************/
INSERT 0 2
Use Case
Records the entire session for debugging or documentation.
Option 6: Output to File (-o)
psql -f test.sql -d postgres -U postgres -o results.txt
If you are getting the permission denied issue,fix that issue by using the below chmod command
chmod 777 results.txt
Check the contents of this file
cat results.txt
Output
CREATE TABLE
INSERT 0 2
id | name
----+-----------
1 | jhon
2 | marc demo
3 | joy
4 | roy
1 | jhon
2 | marc demo
3 | joy
4 | roy
1 | jhon
2 | marc demo
(10 rows)
INSERT 0 2
Use Case
Exports query results for reporting or further processing.
Option 7: Quiet Mode (-q)
psql -f test.sql -d postgres -U postgres -q
Output Behavior
- Removes startup messages.
- Shows only essential output.
Example:
psql:test.sql:4: NOTICE: relation "demo" already exists, skipping
psql:test.sql:4: NOTICE: DDL command executed
id | name
----+-----------
1 | jhon
2 | marc demo
3 | joy
4 | roy
1 | jhon
2 | marc demo
3 | joy
4 | roy
1 | jhon
2 | marc demo
3 | joy
4 | roy
1 | jhon
2 | marc demo
(14 rows)
Use Case
Ideal for automation scripts where minimal output is preferred.
Option 8: Single Step Mode (-s)
psql -f test.sql -d postgres -U postgres -s
Output Behavior
Prompts before each query like this
/**(Single step mode: verify command)******************************************/
CREATE TABLE IF NOT EXISTS demo (
id INT,
name TEXT
);
/**(press return to proceed or enter x and return to cancel)*******************/
psql:test.sql:4: NOTICE: relation "demo" already exists, skipping
psql:test.sql:4: NOTICE: DDL command executed
CREATE TABLE
/**(Single step mode: verify command)******************************************/
insert into demo(id,name)values (1,'jhon'),(2,'marc demo');
/**(press return to proceed or enter x and return to cancel)*******************/
INSERT 0 2
/**(Single step mode: verify command)******************************************/
select * from demo;
/**(press return to proceed or enter x and return to cancel)*******************/
id | name
----+-----------
1 | jhon
2 | marc demo
3 | joy
4 | roy
1 | jhon
2 | marc demo
3 | joy
4 | roy
1 | jhon
2 | marc demo
3 | joy
4 | roy
1 | jhon
2 | marc demo
3 | joy
4 | roy
1 | jhon
2 | marc demo
3 | joy
4 | roy
1 | jhon
2 | marc demo
3 | joy
4 | roy
1 | jhon
2 | marc demo
(26 rows)
/**(Single step mode: verify command)******************************************/
insert into demo(id,name)values (3,'joy'),(4,'roy');
/**(press return to proceed or enter x and return to cancel)*******************/
INSERT 0 2
Use Case
Allows controlled execution of scripts, especially during debugging.
Option 9: Single Line Mode (-S)
psql -d postgres -U postgres -S
Behavior
Each line is executed immediately.
Example:
select version()
This will execute and we get result like this
postgres^# select now()
now
----------------------------------
2026-05-04 21:18:52.176075+05:30
(1 row)
Check the same command when login as normal way in postgres
postgres@cybrosys:/home/cybrosys$ psql
psql (18.3 (Ubuntu 18.3-1.pgdg22.04+1))
Type "help" for help.
postgres=# select now()
postgres-# ;
now
----------------------------------
2026-05-04 21:19:01.089161+05:30
(1 row)
Use Case
Useful for quick, simple queries.
Option 10: No Readline (-n)
psql -d postgres -U postgres -n
Example
postgres@cybrosys:/home/cybrosys$ psql -d postgres -U postgres
psql (18.3 (Ubuntu 18.3-1.pgdg22.04+1))
Type "help" for help.
postgres=# \q
postgres@cybrosys:/home/cybrosys$ psql -d postgres -U postgres -n
psql (18.3 (Ubuntu 18.3-1.pgdg22.04+1))
Type "help" for help.
postgres=# ^[[A^[[A^[[B^[[B
Behavior
- Disables command history
- Arrow keys and shortcuts do not function.
Use Case
Helpful in restricted environments or scripts where readline is unnecessary.
Running the same SQL script multiple times without resetting the data can result in repeated entries, which highlights the importance of writing scripts that handle state properly. Each psql option has a specific role, whether it is improving visibility, isolating errors, or controlling how queries are executed. While working with different system users, file permission conflicts may arise, especially when writing logs or outputs to files. Certain options, such as -S, can alter how input is processed, making it essential to understand their behavior before using them in complex scenarios.
Practicing these options through hands-on execution helps build a clearer understanding of how psql processes input and produces output. This knowledge allows developers to debug more effectively, manage query results efficiently, and interact with PostgreSQL in a more controlled and predictable way.