Two handy examples of the psql \copy meta-command.

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.

Photo by imgix on Unsplash

Importing Data.

Let’s get a description of the staging_assets target table before proceeding:

Next, I have provided an image of the CSV file to be uploaded:

Target file to upload.

Below is the stripped-down syntax I will use for the \copy command examples presented here:

Prior to uploading, I will get a count of records present in the staging_assets table, if any.

The target table is empty. Let me populate it with some data.

The command tag returns a successful COPY 35.

Glancing over the different parts of the command:

• • \copy staging_assets – The target table to perform the operation on.
  • FROM '~/Practice_Data/psql_pipe_tally.csv' – Specifying the file to use.
  • WITH DELIMITER ',' CSV HEADER; – Specifying that this file’s delimiter will be a comma and that it does contain a header.

To verify, I will re-query for another count of the table, along with retrieving 10 rows of data for viewing with a SELECT statement.

Returning 10 rows for viewing:

The \copy command is successful, executing the wholesale upload.

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.

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

An image of the ten_footers.csv file.

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.
• WITH DELIMITER ',' CSV HEADER;– 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.

Have I mentioned how much I love a cup of coffee?!?!

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.