Import CSV into Postgres on ObjectRocket

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

When you need to import data into a Postgres database, a common way to perform bulk imports is to use a comma-delimited, or CSV, file. With a CSV file, you can quickly load the contents of a file into the PostgreSQL table of your choice. In this article, we’ll explain how to import a CSV into Postgres on ObjectRocket, providing step-by-step instructions for each stage of the process.

Prerequisites

In order to follow along with this tutorial, a few key prerequisites need to be in place. First, you’ll need to make sure that the following software was installed and properly configured:

You should also have some basic knowledge of PostgreSQL.

You’ll need to create an instance of Postgres for your ObjectRocket account. This can be accomplished using ObjectRocket Mission Control panel if you haven’t created one yet.

Creating a Postgres Table on Object Rocket

Before we attempt to import our CSV data, we’ll create a new Postgres table on ObjectRocket using the psql utility:

  • First, we’ll connect to our Postgres server instance on ObjectRocket Mission Control. We’ll need the connection details that can be found in the “CONNECT” tab.

  • Then we’ll connect to the Postgres server instance using the psql utility in the terminal. We’ll use the following command:
1
psql -h ingress.w98sujpz.launchpad.objectrocket.cloud -p 4149 -U pguser -d PostgreSQL

Let’s look at this command a bit more closely:

  • We use the -h option to indicate the server IP address or, in this case, the URL.
  • We then use the -p option to specify the port number where the server is listening for client connections.
  • Next, we call the -U option and indicate the name of the user.
  • Finally, we provide the name of our database using the -d option.

NOTE: psql will prompt you to provide the password that you set when you created the user for your Postgres server.

We can now create our sample database and table using the following statements:

First, we’ll create the database and call it stocksdb:

1
CREATE DATABASE stocksdb;

Then we’ll connect to that database using the command \c followed by the name of the database. We can then use the following statement to create our table named items:

1
2
3
4
5
6
CREATE TABLE items(
    id SERIAL PRIMARY KEY NOT NULL,
    name VARCHAR(100),
    price INTEGER,
    quantity INTEGER
);

After creating the table, we’ll need to prepare a CSV file that we’ll import into Postgres. The CSV file will look like this:

NOTE: The location of the file is at C:\temp\sample.csv.

Importing CSV Into Postgres Table

To import the data from the CSV file into the Postgres table, we’ll use the psql command \copy. Here’s how it’s used:

1
psql -h ingress.w98sujpz.launchpad.objectrocket.cloud -U pguser -p 4149 -d stocksdb -c "\copy items FROM 'C:\temp\sample.csv' with (format csv,header true, delimiter ',');"

This command connects to the Postgres server instance on ObjectRocket using the details found in the “CONNECT” tab.

Note that we use the -c option, which tells psql to execute the specified command string. In this example, it executes the following: "\copy items FROM 'C:\temp\sample.csv' with (format csv,header true, delimiter ',');"

After executing the command shown above, we’ll be prompted for the password. If no error occurs, we’ll receive output that looks something like this:

1
COPY 5

To verify that our import was successful, we need to connect to our server and execute a simple SELECT statement. The result should look like this:

1
2
3
4
5
6
7
8
9
stocksdb=> SELECT * FROM items;
 id |        name         | price | quantity
----+---------------------+-------+----------
  1 | close-up toothpaste |     5 |       25
  2 | Oatmeal             |    15 |       30
  3 | Yogurt              |    10 |       13
  4 | Mineral Water       |    20 |      100
  5 | donut               |    20 |      100
(5 ROWS)

Conclusion

If you need to load data into a PostgreSQL table, a CSV file is a natural choice for the task. In this article, we showed you how to import CSV into Postgres on ObjectRocket, providing step-by-step instructions for each part of the process. With this tutorial to guide you, you’ll be prepared to tackle an import into your own PostgreSQL database.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.