Import CSV into Postgres on ObjectRocket
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.
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
psqlutility in the terminal. We’ll use the following command:
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
-hoption to indicate the server IP address or, in this case, the URL.
- We then use the
-poption to specify the port number where the server is listening for client connections.
- Next, we call the
-Uoption and indicate the name of the user.
- Finally, we provide the name of our database using the
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
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
CREATE TABLE items(
id SERIAL PRIMARY KEY NOT NULL,
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
Importing CSV Into Postgres Table
To import the data from the CSV file into the Postgres table, we’ll use the
\copy. Here’s how it’s used:
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:
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:
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
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