Import or export data by using the COPY command

Non-interactive import

Run the following command from your local machine without opening a psql session:

cat $dataFile | psql $dbName -h $host -p $port -U $userName -c "COPY $tableName FROM stdin WITH (DELIMITER '|');"

Replace the placeholders with your actual values:

The cat command reads the local file and pipes its content to psql via stdin. The COPY ... FROM stdin statement then loads that data into the target table. The DELIMITER '|' option specifies that columns in each row are separated by the | character.

For full COPY syntax, see the PostgreSQL COPY documentation.

Example

This example imports data from /tmp/test_data.txt into the testtable table in the testdb database.

1. Confirm the file content:

   cat /tmp/test_data.txt

   Expected output:

   John|1
   Smith|2
   Tom|3
   Jerry|4

2. (Optional) Set the database password as an environment variable to avoid entering it interactively:

   Enter the password directly in the import command when prompted if you skip this step.

   export PGPASSWORD='testpwd'

3. Import the data:

   cat /tmp/test_data.txt | psql testdb -h pgm-bp1131jrd8lr****.pg.rds.aliyuncs.com -p 5432 -U testdbuser -c "COPY testtable FROM stdin WITH (DELIMITER '|');"

   If the import succeeds, the output shows the number of rows imported:

   COPY 4

4. Connect to the RDS for PostgreSQL instance and run a SELECT statement to verify the imported data:

    name   | id
   ----------+----
    John     | 1
    Smith    | 2
    Tom      | 3
    Jerry    | 4
   (4 rows)

Interactive import

If you are already connected to psql, use the \COPY meta-command to import a local file directly:

\COPY $tableName FROM '$dataFile' WITH (DELIMITER '|');

The \COPY command runs client-side and can access files on your local machine without the cat pipe workaround.

For full \COPY syntax, see the psql documentation.

Example

This example imports data from /tmp/test_data.txt into the testtable table.

1. Confirm the file content:

   cat /tmp/test_data.txt

   Expected output:

   John|1
   Smith|2
   Tom|3
   Jerry|4

2. (Optional) Set the database password as an environment variable:

   Enter the password manually when connecting to psql if you skip this step.

   export PGPASSWORD='testpwd'

3. Connect to the testdb database. Replace the connection information with your actual values. For connection details, see View and change the endpoints and port numbers.

   psql -h pgm-bp1131jrd8lr****.pg.rds.aliyuncs.com -p 5432 -U testdbuser -d testdb

4. Run the \COPY command to import the data:

   \COPY testtable FROM '/tmp/test_data.txt' WITH (DELIMITER '|');

   If the import succeeds:

   COPY 4

5. Run a SELECT statement to verify the imported data:

    name   | id
   ----------+----
    John     | 1
    Smith    | 2
    Tom      | 3
    Jerry    | 4
   (4 rows)

Non-interactive export

Run the following command to export a table to a local CSV file without opening a psql session:

psql $dbName -h $host -p $port -U $userName -c "COPY (SELECT * FROM $tableName) TO STDOUT WITH CSV HEADER" -o "$dataFile"

Replace the placeholders with your actual values:

The COPY ... TO STDOUT statement outputs the table data to stdout. The -o flag redirects that output to the specified local file. The CSV HEADER option exports the data in CSV format with a header row containing column names.

Example

This example exports the testtable table to /tmp/test_outdata.

1. (Optional) Set the database password as an environment variable:

   Enter the password directly in the export command when prompted if you skip this step.

   export PGPASSWORD='testpwd'

2. Export the data:

   psql testdb -h pgm-bp1131jrd8lr****.pg.rds.aliyuncs.com -p 5432 -U testdbuser -c "COPY (SELECT * FROM testtable) TO STDOUT WITH CSV HEADER" -o "/tmp/test_outdata"

3. Verify the exported file:

   cat /tmp/test_outdata

   Expected output:

   name,id
   John,1
   Smith,2
   Tom,3
   Jerry,4

Interactive export

If you are already connected to psql, use the \COPY meta-command to export a table to a local CSV file:

\COPY $tableName TO '$dataFile' WITH CSV HEADER;

Example

This example exports the testtable table to /tmp/test_outdata.

1. (Optional) Set the database password as an environment variable:

   Enter the password manually when connecting to psql if you skip this step.

   export PGPASSWORD='testpwd'

2. Connect to the testdb database. Replace the connection information with your actual values. For connection details, see View and change the endpoints and port numbers.

   psql -h pgm-bp1131jrd8lr****.pg.rds.aliyuncs.com -p 5432 -U testdbuser -d testdb

3. Export the data:

   \COPY testtable TO '/tmp/test_outdata' WITH CSV HEADER;

   If the export succeeds:

   COPY 4

4. Exit psql:

   exit

5. Verify the exported file:

   cat /tmp/test_outdata

   Expected output:

   name,id
   John,1
   Smith,2
   Tom,3
   Jerry,4