Import data from CSV

To import data from a CSV file into your Neon database, create the target table first, then run the psql \copy meta-command from a session connected to your database. \copy streams the CSV from your local machine over the existing connection, so it works without any special server-side file access. This topic walks through the process with a simple example.

The instructions require a working installation of psql. The psql client is the native command-line client for Postgres. It provides an interactive session for sending commands to Postgres. For installation instructions, see How to install psql.

The following example uses the ready-to-use neondb database that is created with your Neon project, a table named customer, and a data file named customer.csv. Data is loaded from the customer.csv file into the customer table.

1. Connect to your database

   Connect to the neondb database using psql. For example:

   psql "<your_neon_database_connection_string>"

   You can find your connection string on your Neon Project Dashboard. Click on the Connect button. Use the drop-down menu to copy a full psql connection command.

   note

   For more information about connecting to Neon with psql, see Connect with psql.

2. Create the target table

   Create the customer table: the table you are importing to must exist in your database and the columns must match your CSV file.

   CREATE TABLE customer (
      id SERIAL,
      first_name VARCHAR(50),
      last_name VARCHAR(50),
      email VARCHAR(255),
      PRIMARY KEY (id)
   )

   tip

   You can also create tables using the SQL Editor in the Neon Console. See Query with Neon's SQL Editor.

3. Prepare the CSV file

   Prepare a customer.csv file with the following data. Note that the columns in the CSV file match the columns in the table you created in the previous step.

   First Name,Last Name,Email
   1,Casey,Smith,casey.smith@example.com
   2,Sally,Jones,sally.jones@example.com

4. Load the data

   From your psql prompt, load the data from the customer.csv file using the \copy option.

   \copy customer FROM '/path/to/customer.csv' DELIMITER ',' CSV HEADER

   If the command runs successfully, it returns the number of records copied to the database:

   COPY 2

   For more information about the \copy option, refer to the psql reference, in the PostgreSQL Documentation.