When working with PostgreSQL, many developers focus only on SQL queries such as SELECT, INSERT, UPDATE, and DELETE. But the PostgreSQL interactive terminal, psql, offers another powerful layer of productivity through meta-commands.
These commands begin with a backslash (\) and help you perform file operations, export data, import CSV files, run scripts, redirect output, print messages, and automate tasks directly from the terminal.
Why psql Meta-Commands Matter?
Meta-commands can save time and simplify your workflow. Instead of switching between your terminal, text editor, and SQL client, you can do many tasks in one place.
They are especially useful for:- Exporting query results
- Importing CSV files
- Running SQL scripts
- Logging output
- Creating reports
- Printing messages in automation scripts
- Debugging and scripting database tasks
Setting Up the Example Database
First, create a sample database and connect to it.
create database test;
\c test
Output:
CREATE DATABASE
You are now connected to database "test" as user "postgres".
Now create a sample table:
CREATE TABLE employees (
id SERIAL,
name VARCHAR(50),
salary NUMERIC
);
Insert sample records:
insert into employees (id,name,salary)values(1,'marc demo',35000),(2,'jhon',45000),(3,'paulson',49000);
Check the data:
select * from employees;
Output:
id | name | salary
----+-----------+--------
1 | marc demo | 35000
2 | jhon | 45000
3 | paulson | 49000
How to Use \copy in PostgreSQL
The \copy command is used to transfer data between a table and a file on the client machine. Unlike SQL COPY, \copy works with files accessible from your local system.
Export Table Data to CSV
\copy employees TO '/tmp/employees.csv' CSV HEADER
Output:
COPY 3
Check the file:
\! cat /tmp/employees.csv
Output:
id,name,salary
1,marc demo,35000
2,jhon,45000
3,paulson,49000
Import CSV Data into Table
Now import the same file again:
\copy employees FROM '/tmp/employees.csv' CSV HEADER
Check table data:
select * from employees;
Output:
id | name | salary
----+-----------+--------
1 | marc demo | 35000
2 | jhon | 45000
3 | paulson | 49000
1 | marc demo | 35000
2 | jhon | 45000
3 | paulson | 49000
The records were inserted again, so duplicates appeared.
Export Query Result to CSV
You can export query results directly.
\copy (SELECT name, salary FROM employees WHERE salary > 25000) TO '/tmp/high_salary.csv' CSV HEADER
Check the file:
\! cat /tmp/high_salary.csv
Output:
name,salary
marc demo,35000
jhon,45000
paulson,49000
marc demo,35000
jhon,45000
paulson,49000
How to Use \echo
The \echo command prints messages to the screen. It is useful in scripts when you want to show progress messages.
Basic Example
\echo Hello odoo_user
Output:
Hello odoo_user
Without New Line
\echo -n Loading...
Output:
Loading...
The cursor stays on the same line.
Script Style Example
\echo Starting Script
SELECT now();
\echo Script Completed
Output:
Starting Script
now
----------------------------------
2026-04-27 19:28:36.775427+05:30
Script Completed
How to Use \i to Run SQL Files
The \i command executes SQL commands from a file.
Create a file named sample.sql:
nano /tmp/sample.sql
Insert these queries into this sample.sql file
CREATE TABLE test1(id INT);
INSERT INTO test1 VALUES (1);
SELECT * FROM test1;
Run it:
\i /tmp/sample.sql
Output:
CREATE TABLE
INSERT 0 1
id
----
1
Now verify:
select * from test1;
Output:
id
----
1
How to Use \ir for Relative Scripts
The \ir command works like \i, but the file path is relative to the current script location.
Suppose your folder structure is:
projects/
+-- main.sql
+-- scripts/
+-- insert.sql
Content of main.sql:
CREATE TABLE demo(id INT);
\ir scripts/insert.sql
SELECT * FROM demo;
Content of scripts/insert.sql:
INSERT INTO demo VALUES (100);
Run:
\i /home/cybrosys/projects/main.sql
Output:
CREATE TABLE
INSERT 0 1
id
-----
100
This is very useful for modular SQL projects.
How to Use \o to Redirect Output
The \o command sends all query output to a file or pipe.
Save Query Results to File
\o /tmp/output.txt
select * from employees;
select now();
\o
The last \o resets output back to the screen. Check the file:
\! cat /tmp/output.txt
Output:
id | name | salary
----+-----------+--------
1 | marc demo | 35000
2 | jhon | 45000
3 | paulson | 49000
1 | marc demo | 35000
2 | jhon | 45000
3 | paulson | 49000
now
----------------------------------
2026-04-27 19:31:28.183512+05:30
How to Use \qecho
The \qecho command writes text into the current \o output destination. It does not print to the screen.
Example Report Generation
\o /tmp/report.txt
\qecho Employee Report
SELECT * FROM employees;
\o
Check the file:
\! cat /tmp/report.txt
Output:
Employee Report
id | name | salary
----+-----------+--------
1 | marc demo | 35000
2 | jhon | 45000
3 | paulson | 49000
1 | marc demo | 35000
2 | jhon | 45000
3 | paulson | 49000
This is a simple way to create custom reports.
How to Use \warn
The \warn command writes messages to standard error. It is useful for warnings or debugging output in scripts.
Basic Warning Example
\warn Warning: Table is empty
Output:
Warning: Table is empty
Without New Line
\warn -n Checking...
Output:
Checking...
Once you start using these meta-commands regularly, your PostgreSQL workflow becomes faster and cleaner.
You can:
- Export reports in seconds
- Reuse SQL files
- Organize large scripts
- Generate logs
- Automate imports and exports
- Improve terminal productivity
For database administrators, developers, and performance engineers, these commands are essential. Most beginners use psql only for running SQL queries, but the real power of the terminal comes from its built-in meta-commands.Commands like \copy, \i, \ir, \o, \qecho, \echo, and \warn turn psql into a complete database automation tool.
If you practice these examples regularly, you will become much more efficient with PostgreSQL administration and scripting. Start with small tasks like exporting CSV files or running scripts, then move to larger automation workflows. That is where psql becomes more than a query tool. It becomes a productivity tool.