Import & Export Data from PostgreSQL

Import Data to Table

To import data into a PostgreSQL table, we can use the following command:

psql -h 127.0.0.1 -p 5432 -d demo -U demouser -W -c "\COPY sales.dept FROM '/Users/sauravmitra/Demo/dept.csv' DELIMITER ',' CSV HEADER"

This command imports data from a comma-delimited file with a header into the sales.dept table. We can also specify specific columns to import by adding them in parentheses, like this:

psql -h 127.0.0.1 -p 5432 -d demo -U demouser -W -c "\COPY sales.dept (deptno, dname) FROM '/Users/sauravmitra/Demo/dept.csv' DELIMITER ',' CSV HEADER"

Export Data from Table

To export data from a PostgreSQL table, we can use the following command:

psql -h 127.0.0.1 -p 5432 -d demo -U demouser -W -c "\COPY sales.dept TO '/Users/sauravmitra/Demo/dept.csv' DELIMITER ',' CSV HEADER"

This command exports data from the sales.dept table into a comma-delimited file with a header. Again, we can specify specific columns to export by adding them in parentheses.

psql -h 127.0.0.1 -p 5432 -d demo -U demouser -W -c "\COPY sales.dept (deptno, dname) TO '/Users/sauravmitra/Demo/dept.csv' DELIMITER ',' CSV HEADER"

Export Data Based on SQL Query

But what if we want to export data based on a specific SQL query? That's where things get really powerful. We can use the following command:

psql -h 127.0.0.1 -p 5432 -d demo -U demouser -W -c "\COPY (SELECT * FROM sales.dept WHERE deptno < 5) TO '/Users/sauravmitra/Demo/dept_filter.csv' DELIMITER ',' CSV HEADER"

This command exports data from the sales.dept table, but only for rows where deptno is less than 5. The resulting file will contain only those rows.

Export Data Based on Complex SQL Query

And finally, what if we have a complex SQL query that we want to use to export data? We can create a SQL query file and then use the following command:

emp_dept.sql

WITH employee AS (
    SELECT 
        empno, ename, mgrno, deptno, sal
    FROM sales.emp
),
department AS (
    SELECT
        deptno AS dnum, dname
    FROM sales.dept 
),
manager AS (
    SELECT
        empno AS mgrnum, ename AS mgrname
    FROM sales.emp
),
result AS (
    SELECT
       empno, ename, deptno, dname, sal, mgrno, mgrname
    FROM employee
    INNER JOIN department
    ON employee.deptno = department.dnum
    LEFT OUTER JOIN manager
    ON employee.mgrno = manager.mgrnum
) 
SELECT * FROM result

psql -h 127.0.0.1 -p 5432 -d demo -U demouser -W -c "\COPY ($(</Users/sauravmitra/Demo/emp_dept.sql)) TO '/Users/sauravmitra/Demo/emp_dept.csv' DELIMITER ',' CSV HEADER"

This command exports data based on the complex SQL query in the emp_dept.sql file, and saves it to a comma-delimited file with a header.

In this article, we've seen how to use the psql COPY utility command to import and export data from PostgreSQL. Whether we're working with small datasets or large ones, these commands can save us time and reduce errors. So next time you need to manage your data, give them a try!