In this blog post, I will cover example uses of the psql\copy meta-command, for client-side work with the PostgreSQL database server. This command allows users who do not have Superuser privileges, to perform COPY type SQL commands.

Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.

I will be using Xubuntu Linux 16.04.2 LTS (Xenial Xerus) and PostgreSQL 10.0 for these exercises.

To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!

Writing to local files.

With importing or uploading data to our tables, we can easily reverse this operation to write back to a file on the local system.

Suppose a task has been given to write to a file named ten_footers.csv, all the records from the staging_assets table that have a pipe_length of 10 feet or less. You wonder how can \copy accomplish that?

Let’s find out.

Before anything, I need to establish a query to retrieve only those records matching the requirement.

This one should do it.

fab_tracking=>SELECT *FROM staging_assetsWHERE pipe_length <=10.00;

The following \copy command meets the requirements of providing a CSV file format:

The newly written ten_footers.csv file, complete with the required data.

Briefly looking at the supplied arguments.

(SELECT * FROM staging_assets WHERE pipe_length <=10.00)– The query used to retrieve the correct records in accordance with the specification.

TO'~/Practice_Data/ten_footers.csv'
– Write the results of the above query to the provided file, which is ten_footers.csv.

WITHDELIMITER','CSVHEADER;– Designating a comma for the delimiter and using headers.

This is my initial exploration of the \copy command however, I plan to discover multiple applicable uses of it in the near future.

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar! Be sure and visit the “Best Of” page for a collection of my best blog posts.

Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.

On this website we use first or third-party tools that store small files (cookie) on your device. Cookies are normally used to allow the site to run properly (technical cookies), to generate navigation usage reports (statistics cookies) and to suitable advertise our services/products (profiling cookies). We can directly use technical cookies, but you have the right to choose whether or not to enable statistical and profiling cookies. Enabling these cookies, you help us to offer you a better experience.